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.FileOutputStream; import java.io.Serializable; import java.math.BigDecimal; import java.util.Date; import java.text.DateFormat; 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 javax.management.openmbean.SimpleType; 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 DamayanListingNoticeController implements Serializable { /** * Creates a new instance of DamayanListingNoticeController */ public DamayanListingNoticeController() { } /* * properties */ @ManagedProperty(value = "#{damayanListingNoticeData}") private DamayanListingNoticeData damayanListingNoticeData; @ManagedProperty(value = "#{myMessages}") private MyMessages myMessages; @ManagedProperty(value = "#{exportData}") private ExportData exportData; /* * getter setter */ public DamayanListingNoticeData getDamayanListingNoticeData() { return damayanListingNoticeData == null ? damayanListingNoticeData = new DamayanListingNoticeData() : damayanListingNoticeData; } public void setDamayanListingNoticeData(DamayanListingNoticeData damayanListingNoticeData) { this.damayanListingNoticeData = damayanListingNoticeData; } 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() { getDamayanListingNoticeData().beanclear(); FacesContext.getCurrentInstance().getExternalContext().getSessionMap().put("damayanListingNoticeController", null); } public String stringFormat() { String query; query = "SELECT row_number() OVER (ORDER BY x.acctno) AS row_number, " + "x.acctno AS dm_acctno, " + "y.acctno AS sc_acctno, " + "x.acct_name, " + "x.acct_status, " + "x.payment_type, " + "x.pdform, " + "x.balance, " + "x.account_code " + "FROM (SELECT apd.acctno, " + "cdm.acct_name, " + "apd.acct_status, " + "cdm.pdform, " + "dod.balance, " + "coa.account_code, " + "cdm.payment_type, " + "apd.primary_holder " + "FROM coop_fin_class_dm cdm " + "JOIN coop_fin_acct_profile_dtl apd ON cdm.acctno = apd.acctno " + "JOIN damayan_listing_distinct_on_dm ('" + getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateFrom(), "yyyy-MM-dd") + "' , '" + getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateTo(), "yyyy-MM-dd") + "') dod ON apd.acctno = dod.acctno " + "JOIN coop_fin_chart_of_accounts coa ON apd.account_code = coa.account_code " + "WHERE apd.acctno ~ 'DM' " + "AND apd.primary_holder IN ( " + "SELECT DISTINCT primary_holder " + "FROM coop_fin_acct_profile_dtl " + "WHERE (acctno ~ 'SC' OR acctno ~ 'SD') " + "AND acct_status <> 'C') " + "AND coa.account_code = '231203030' "; // int ctr = 1; // for (String x : getDamayanListingNoticeData().getAccountCodes()) { // if (getDamayanListingNoticeData().getAccountCodes().size() == 1) { // query += "'" + x + "'"; // } else { // if (ctr == getDamayanListingNoticeData().getAccountCodes().size()) { // query += "'" + x + "'"; // } else { // query += "'" + x + "',"; // } // } // ctr++; // } // query += ") "; if (getDamayanListingNoticeData().getSelectedDue().contains("510 and above")) { query += "AND dod.balance >= 510 "; } else if (getDamayanListingNoticeData().getSelectedDue().contains("509 and below")) { query += "AND (dod.balance > 0 " + "AND dod.balance <= 509) "; } else if (getDamayanListingNoticeData().getSelectedDue().contains("All Dues without 0")) { query += "AND dod.balance > 0 "; } else { query += ""; } query += "ORDER BY apd.acctno, apd.acct_create_date DESC) x " + "LEFT JOIN (SELECT DISTINCT ON (apd.primary_holder) " + "apd.primary_holder, " + "apd.acctno, " + "apd.acct_status " + "FROM coop_fin_acct_profile_dtl apd " + "WHERE (apd.acctno ~ 'SC' OR apd.acctno ~ 'SD') " + "AND apd.acct_status <> 'C' " + "ORDER BY apd.primary_holder, " + "CASE apd.acct_status WHEN 'A' THEN 1 " + "WHEN 'O' THEN 2 " + "WHEN '' THEN 3 " + "WHEN 'C' THEN 4 " + "END " + ") y ON x.primary_holder = y.primary_holder "; return query; } public void runNotice() { getDamayanListingNoticeData().getPrintController().setSelectedBatch(null); getDamayanListingNoticeData().getPrintController().setSelectedBatch(""); getDamayanListingNoticeData().setDamayanListSummaryPerAccountCode(null); getDamayanListingNoticeData().setDamayanListSummaryPerBatch(null); getDamayanListingNoticeData().setSelectedAccountCode(null); getDamayanListingNoticeData().setDamayanList(null); getDamayanListingNoticeData().setDamayanList( getDamayanListingNoticeData().getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory() .createEntityManager().createNativeQuery(stringFormat()).getResultList()); getDamayanListingNoticeData().setAccountCodesFiltered(getDamayanListingNoticeData().getAccountCodes()); getDamayanListingNoticeData().getPrintController() .method0(getDamayanListingNoticeData().getDamayanList().size()); getGrandTotal(); getDamayanListingNoticeData().setRunComplete(true); FacesContext context = FacesContext.getCurrentInstance(); if (getDamayanListingNoticeData().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 query; // // query = "SELECT row_number() OVER (ORDER BY x.acctno) AS row_number, " // + "x.acctno AS dm_acctno, " // + "y.acctno AS sc_acctno, " // + "x.acct_name, " // + "x.balance, " // + "x.payment_type " // + "FROM (SELECT cdm.acctno, " // + "apd.acct_profile_master_no, " // + "cdm.acct_name, " // + "dod.balance, " // + "dod.statement_date, " // + "coa.account_code, " // + "cdm.payment_type " // + "FROM coop_fin_class_dm cdm " // + "JOIN coop_fin_acct_profile_dtl apd ON cdm.acctno = apd.acctno " // + "JOIN damayan_listing_distinct_on_dm ('" + getDamayanListingNoticeData().getCustomDate().formatDate(getDamayanListingNoticeData().getAcctCreateDateFrom(), "yyyy-MM-dd") + "' , '" + getDamayanListingNoticeData().getCustomDate().formatDate(getDamayanListingNoticeData().getAcctCreateDateTo(), "yyyy-MM-dd") + "') dod ON apd.acctno = dod.acctno " // + "JOIN coop_fin_chart_of_accounts coa ON apd.account_code = coa.account_code " // + "WHERE apd.acctno ~ 'DM' " //// + "AND apd.acct_status = 'A' " // + "AND coa.account_code = '" + accountCodeParam + "' "; // // if (getDamayanListingNoticeData().getSelectedDue().contains("510 and above")) { // query += "AND dod.balance >= 510 "; // } else if (getDamayanListingNoticeData().getSelectedDue().contains("509 and below")) { // query += "AND (dod.balance > 0 " // + "AND dod.balance <= 509) "; // } else if (getDamayanListingNoticeData().getSelectedDue().contains("All Dues without 0")) { // query += "AND dod.balance > 0 "; // } else { // query += ""; // } // // query += "ORDER BY apd.acct_profile_master_no, apd.acctno, apd.acct_create_date DESC) x " // + "LEFT JOIN ( SELECT DISTINCT apd.acct_profile_master_no, " // + "apd.acctno, " // + "hp.birthdate " // + "FROM coop_fin_acct_profile_dtl apd " // + "JOIN coop_fin_ha_profile hap ON apd.acct_profile_master_no = hap.acct_profile_master_no " // + "JOIN coop_fin_holder_profile hp ON hap.holder_profile_id = hp.holder_profile_id " // + "JOIN damayan_listing_distinct_on_sc dln ON apd.acctno = dln.acctno " // + "WHERE apd.acctno ~ 'SC' OR apd.acctno ~ 'SD' " // + "ORDER BY apd.acct_profile_master_no, apd.acctno) y ON x.acct_profile_master_no = y.acct_profile_master_no "; // // return query; // } public String queryBatchMe(String selectedBatchParam) { String query; query = "SELECT * FROM( " + "SELECT row_number() OVER (ORDER BY x.acctno) AS row_number, " + "x.acctno AS dm_acctno, " + "y.acctno AS sc_acctno, " + "x.acct_name, " + "x.acct_status, " + "x.payment_type, " + "x.pdform, " + "x.balance " + "FROM (SELECT apd.acctno, " + "cdm.acct_name, " + "apd.acct_status, " + "cdm.pdform, " + "dod.balance, " + "coa.account_code, " + "cdm.payment_type, " + "apd.primary_holder " + "FROM coop_fin_class_dm cdm " + "JOIN coop_fin_acct_profile_dtl apd ON cdm.acctno = apd.acctno " + "JOIN damayan_listing_distinct_on_dm ('" + getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateFrom(), "yyyy-MM-dd") + "' , '" + getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateTo(), "yyyy-MM-dd") + "') dod ON apd.acctno = dod.acctno " + "JOIN coop_fin_chart_of_accounts coa ON apd.account_code = coa.account_code " + "WHERE apd.acctno ~ 'DM' " + "AND apd.primary_holder IN ( " + "SELECT DISTINCT primary_holder " + "FROM coop_fin_acct_profile_dtl " + "WHERE (acctno ~ 'SC' OR acctno ~ 'SD') " + "AND acct_status <> 'C') " + "AND coa.account_code = '231203030' "; if (getDamayanListingNoticeData().getSelectedDue().contains("510 and above")) { query += "AND dod.balance >= 510 "; } else if (getDamayanListingNoticeData().getSelectedDue().contains("509 and below")) { query += "AND (dod.balance > 0 " + "AND dod.balance <= 509) "; } else if (getDamayanListingNoticeData().getSelectedDue().contains("All Dues without 0")) { query += "AND dod.balance > 0 "; } else { query += ""; } query += "ORDER BY apd.acctno, apd.acct_create_date DESC) x " + "LEFT JOIN (SELECT DISTINCT ON (apd.primary_holder) " + "apd.primary_holder, " + "apd.acctno, " + "apd.acct_status " + "FROM coop_fin_acct_profile_dtl apd " + "WHERE (apd.acctno ~ 'SC' OR apd.acctno ~ 'SD') " + "AND apd.acct_status <> 'C' " + "ORDER BY apd.primary_holder, " + "CASE apd.acct_status WHEN 'A' THEN 1 " + "WHEN 'O' THEN 2 " + "WHEN '' THEN 3 " + "WHEN 'C' THEN 4 " + "END " + ") y ON x.primary_holder = y.primary_holder" + ") i " + "WHERE i.row_number " + selectedBatchParam + " "; return query; } public String querySumGrandTotal() { String query; query = "SELECT SUM(i.balance) FROM(" + "SELECT row_number() OVER (ORDER BY x.acctno) AS row_number, " + "x.balance " + "FROM (SELECT apd.acctno, " + "dod.balance, " + "apd.primary_holder " + "FROM coop_fin_class_dm cdm " + "JOIN coop_fin_acct_profile_dtl apd ON cdm.acctno = apd.acctno " + "JOIN damayan_listing_distinct_on_dm ('" + getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateFrom(), "yyyy-MM-dd") + "' , '" + getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateTo(), "yyyy-MM-dd") + "') dod ON apd.acctno = dod.acctno " + "JOIN coop_fin_chart_of_accounts coa ON apd.account_code = coa.account_code " + "WHERE apd.acctno ~ 'DM' " + "AND apd.primary_holder IN ( " + "SELECT DISTINCT primary_holder " + "FROM coop_fin_acct_profile_dtl " + "WHERE (acctno ~ 'SC' OR acctno ~ 'SD') " + "AND acct_status <> 'C') " + "AND coa.account_code = '231203030' "; // int ctr = 1; // for (String x : getDamayanListingNoticeData().getAccountCodes()) { // if (getDamayanListingNoticeData().getAccountCodes().size() == 1) { // query += "'" + x + "'"; // } else { // if (ctr == getDamayanListingNoticeData().getAccountCodes().size()) { // query += "'" + x + "'"; // } else { // query += "'" + x + "',"; // } // } // ctr++; // } // query += ") "; if (getDamayanListingNoticeData().getSelectedDue().contains("510 and above")) { query += "AND dod.balance >= 510 "; } else if (getDamayanListingNoticeData().getSelectedDue().contains("509 and below")) { query += "AND (dod.balance > 0 " + "AND dod.balance <= 509) "; } else if (getDamayanListingNoticeData().getSelectedDue().contains("All Dues without 0")) { query += "AND dod.balance > 0 "; } else { query += ""; } query += "ORDER BY apd.acctno, apd.acct_create_date DESC) x " + "LEFT JOIN (SELECT DISTINCT ON (apd.primary_holder) " + "apd.primary_holder, " + "apd.acctno, " + "apd.acct_status " + "FROM coop_fin_acct_profile_dtl apd " + "WHERE (apd.acctno ~ 'SC' OR apd.acctno ~ 'SD') " + "AND apd.acct_status <> 'C' " + "ORDER BY apd.primary_holder, " + "CASE apd.acct_status WHEN 'A' THEN 1 " + "WHEN 'O' THEN 2 " + "WHEN '' THEN 3 " + "WHEN 'C' THEN 4 " + "END " + ") y ON x.primary_holder = y.primary_holder " + ") i "; return query; } public String queryCountGrandTotal() { String query; query = "SELECT COUNT(i.balance) FROM(" + "SELECT row_number() OVER (ORDER BY x.acctno) AS row_number, " + "x.balance " + "FROM (SELECT apd.acctno, " + "dod.balance, " + "apd.primary_holder " + "FROM coop_fin_class_dm cdm " + "JOIN coop_fin_acct_profile_dtl apd ON cdm.acctno = apd.acctno " + "JOIN damayan_listing_distinct_on_dm ('" + getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateFrom(), "yyyy-MM-dd") + "' , '" + getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateTo(), "yyyy-MM-dd") + "') dod ON apd.acctno = dod.acctno " + "JOIN coop_fin_chart_of_accounts coa ON apd.account_code = coa.account_code " + "WHERE apd.acctno ~ 'DM' " + "AND apd.primary_holder IN ( " + "SELECT DISTINCT primary_holder " + "FROM coop_fin_acct_profile_dtl " + "WHERE (acctno ~ 'SC' OR acctno ~ 'SD') " + "AND acct_status <> 'C') " + "AND coa.account_code = '231203030' "; // int ctr = 1; // for (String x : getDamayanListingNoticeData().getAccountCodes()) { // if (getDamayanListingNoticeData().getAccountCodes().size() == 1) { // query += "'" + x + "'"; // } else { // if (ctr == getDamayanListingNoticeData().getAccountCodes().size()) { // query += "'" + x + "'"; // } else { // query += "'" + x + "',"; // } // } // ctr++; // } // // query += ") "; if (getDamayanListingNoticeData().getSelectedDue().contains("510 and above")) { query += "AND dod.balance >= 510 "; } else if (getDamayanListingNoticeData().getSelectedDue().contains("509 and below")) { query += "AND (dod.balance > 0 " + "AND dod.balance <= 509) "; } else if (getDamayanListingNoticeData().getSelectedDue().contains("All Dues without 0")) { query += "AND dod.balance > 0 "; } else { query += ""; } query += "ORDER BY apd.acctno, apd.acct_create_date DESC) x " + "LEFT JOIN ( SELECT DISTINCT ON (apd.primary_holder) " + "apd.primary_holder, " + "apd.acctno, " + "apd.acct_status " + "FROM coop_fin_acct_profile_dtl apd " + "WHERE (apd.acctno ~ 'SC' OR apd.acctno ~ 'SD') " + "AND apd.acct_status <> 'C' " + "ORDER BY apd.primary_holder, " + "CASE apd.acct_status WHEN 'A' THEN 1 " + "WHEN 'O' THEN 2 " + "WHEN '' THEN 3 " + "WHEN 'C' THEN 4 " + "END " + ") y ON x.primary_holder = y.primary_holder " + ") i "; return query; } public String querySubTotal() { String query; query = "SELECT SUM(i.balance) FROM(" + "SELECT row_number() OVER (ORDER BY x.acctno) AS row_number, " + "x.balance " + "FROM (SELECT apd.acctno, " + "dod.balance, " + "apd.primary_holder " + "FROM coop_fin_class_dm cdm " + "JOIN coop_fin_acct_profile_dtl apd ON cdm.acctno = apd.acctno " + "JOIN damayan_listing_distinct_on_dm ('" + getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateFrom(), "yyyy-MM-dd") + "' , '" + getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateTo(), "yyyy-MM-dd") + "') dod ON apd.acctno = dod.acctno " + "JOIN coop_fin_chart_of_accounts coa ON apd.account_code = coa.account_code " + "WHERE apd.acctno ~ 'DM' " + "AND apd.primary_holder IN ( " + "SELECT DISTINCT primary_holder " + "FROM coop_fin_acct_profile_dtl " + "WHERE (acctno ~ 'SC' OR acctno ~ 'SD') " + "AND acct_status <> 'C') " + "AND coa.account_code = '231203030' "; if (getDamayanListingNoticeData().getSelectedDue().contains("510 and above")) { query += "AND dod.balance >= 510 "; } else if (getDamayanListingNoticeData().getSelectedDue().contains("509 and below")) { query += "AND (dod.balance > 0 " + "AND dod.balance <= 509) "; } else if (getDamayanListingNoticeData().getSelectedDue().contains("All Dues without 0")) { query += "AND dod.balance > 0 "; } else { query += ""; } query += "ORDER BY apd.acctno, apd.acct_create_date DESC) x " + "LEFT JOIN ( SELECT DISTINCT ON (apd.primary_holder) " + "apd.primary_holder, " + "apd.acctno, " + "apd.acct_status " + "FROM coop_fin_acct_profile_dtl apd " + "WHERE (apd.acctno ~ 'SC' OR apd.acctno ~ 'SD') " + "AND apd.acct_status <> 'C' " + "ORDER BY apd.primary_holder, " + "CASE apd.acct_status WHEN 'A' THEN 1 " + "WHEN 'O' THEN 2 " + "WHEN '' THEN 3 " + "WHEN 'C' THEN 4 " + "END " + ") y ON x.primary_holder = y.primary_holder " + ") i "; return query; } // public void accountCodeMe() { // // try { // //clear // getDamayanListingNoticeData().getPrintController().setSelectedBatch(null); // getDamayanListingNoticeData().setLastAcctCode(false); // getDamayanListingNoticeData().setGrandTotalBool(false); // // //SetLastAcctCode to true // if (getDamayanListingNoticeData().getAccountCodesFiltered().isEmpty()) { // getMyMessages().messageWarn("There are no selected accounts yet.", ""); // } else if ((getDamayanListingNoticeData().getAccountCodesFiltered().size() == 1) // || (getDamayanListingNoticeData().getSelectedAccountCode() // .equals(getDamayanListingNoticeData().getAccountCodesFiltered() // .get(getDamayanListingNoticeData().getAccountCodesFiltered().size() - 1)))) { // getDamayanListingNoticeData().setLastAcctCode(true); // } // // //setDamayanListSummaryPerAccountCode // if (!getDamayanListingNoticeData().getSelectedAccountCode().isEmpty()) { // getDamayanListingNoticeData().setDamayanListSummaryPerAccountCode(getDamayanListingNoticeData().getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory().createEntityManager().createNativeQuery(queryAccountCodeMe(getDamayanListingNoticeData().getSelectedAccountCode())).getResultList()); // } // //method 0 / getDamayanListSummaryPerAccountCode.size // getDamayanListingNoticeData().getPrintController().method0(getDamayanListingNoticeData().getDamayanListSummaryPerAccountCode().size()); // // } catch (NullPointerException ne) { // if (getDamayanListingNoticeData().getSelectedAccountCode().isEmpty()) { // getMyMessages().messageInfo("You haven't selected account title.", ""); // System.out.println("Selected Account title is " + getDamayanListingNoticeData().getSelectedAccountCode() + "."); // } else { // System.out.println("Your " + ne + "is null."); // } // } // } public void batchMe() { getDamayanListingNoticeData().setSubTotalBool(Boolean.FALSE); getDamayanListingNoticeData().setGrandTotalBool(Boolean.FALSE); if ((getDamayanListingNoticeData().getPrintController().getBatchIndicators().size() == 1) || (getDamayanListingNoticeData().getPrintController().getBatchIndicators() .get(getDamayanListingNoticeData().getPrintController().getBatchIndicators().size() - 1) .equals(getDamayanListingNoticeData().getPrintController().getSelectedBatch()))) { getSubTotal(); if (getDamayanListingNoticeData().getLastAcctCode() == true) { getDamayanListingNoticeData().setGrandTotalBool(true); getGrandTotal(); } } if (!getDamayanListingNoticeData().getPrintController().getBatchIndicators().isEmpty()) { getDamayanListingNoticeData().setDamayanListSummaryPerBatch(getDamayanListingNoticeData() .getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory().createEntityManager() .createNativeQuery( queryBatchMe(getDamayanListingNoticeData().getPrintController().getSelectedBatch())) .getResultList()); } } public void getSubTotal() { getDamayanListingNoticeData().setSubTotalBool(true); getDamayanListingNoticeData().setSubTotal((BigDecimal) getDamayanListingNoticeData() .getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory().createEntityManager() .createNativeQuery(querySubTotal()).getSingleResult()); } public void getGrandTotal() { getDamayanListingNoticeData().setGrandTotal((BigDecimal) getDamayanListingNoticeData() .getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory().createEntityManager() .createNativeQuery(querySumGrandTotal()).getSingleResult()); getDamayanListingNoticeData().setTotalAccounts((Long) getDamayanListingNoticeData() .getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory().createEntityManager() .createNativeQuery(queryCountGrandTotal()).getSingleResult()); } public void export0() { 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 Notice", "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 Notice " + getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateFrom(), "MMMM dd yyyy") + " to " + getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateTo(), "MMMM dd yyyy") + " (Amount Due - " + getDamayanListingNoticeData().getSelectedDue() + ")"); dataRow = sheet.createRow((short) 0); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue("DAMAYAN LISTING NOTICE"); cell.setCellStyle(boldStyle); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue(getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateFrom(), "MMMM dd yyyy") + " to " + getDamayanListingNoticeData().getCustomDate() .formatDate(getDamayanListingNoticeData().getAcctCreateDateTo(), "MMMM dd yyyy") + " (Amount Due - " + getDamayanListingNoticeData().getSelectedDue() + ")"); 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("Account Status"); cell.setCellStyle(boldStyle); columnNo = 4; cell = dataRow.createCell(columnNo++); cell.setCellValue("Payment Type"); cell.setCellStyle(boldStyle); columnNo = 5; cell = dataRow.createCell(columnNo++); cell.setCellValue("PD Form"); cell.setCellStyle(boldStyle); columnNo = 6; cell = dataRow.createCell(columnNo++); cell.setCellValue("Balance"); cell.setCellStyle(boldStyle); System.out.println("Start content"); for (int i = 0; i < getDamayanListingNoticeData().getDamayanList().size(); i++) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); //DM Account No columnNo = 0; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getDamayanListingNoticeData().getDamayanList().get(i)[1]); cell.setCellStyle(cellStyle); System.out.println("DM Account No"); //SC Account No columnNo = 1; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getDamayanListingNoticeData().getDataConvert() .convertSdToScAcctno(getDamayanListingNoticeData().getDamayanList().get(i)[2].toString())); cell.setCellStyle(cellStyle); System.out.println("SC Account No"); //Account Name columnNo = 2; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getDamayanListingNoticeData().getDamayanList().get(i)[3]); cell.setCellStyle(cellStyle); System.out.println("Account Name"); //Account Status columnNo = 3; cell = dataRow.createCell(columnNo++); cell.setCellValue(getDamayanListingNoticeData().getDataConvert().acctStatusConvert( getDamayanListingNoticeData().getDamayanList().get(i)[4].toString().charAt(0))); cell.setCellStyle(cellStyle); System.out.println("Account Status"); //Payment Type columnNo = 4; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getDamayanListingNoticeData().getDamayanList().get(i)[5].toString() .replace("true", "Auto Deduction").replace("false", "Manual Deduction")); cell.setCellStyle(cellStyle); System.out.println("Payment Type"); //PD Form try { columnNo = 5; cell = dataRow.createCell(columnNo++); cell.setCellValue((String) getDamayanListingNoticeData().getDamayanList().get(i)[6].toString()); cell.setCellStyle(cellStyle); System.out.println("PD Form"); } catch (Exception e) { System.out.println("PD Form null " + e); } //Balance columnNo = 6; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getDamayanListingNoticeData().getDataConvert() .convertAmount((BigDecimal) getDamayanListingNoticeData().getDamayanList().get(i)[7])); cell.setCellStyle(cellStyle); System.out.println("Balance"); if (i == getDamayanListingNoticeData().getDamayanList().size() - 1) { dataRow = sheet.createRow(dataRow.getRowNum() + 1); dataRow = sheet.createRow(dataRow.getRowNum() + 1); columnNo = 4; cell = dataRow.createCell(columnNo++); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); dataRow.createCell(columnNo++).setCellValue(""); columnNo = 6; cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getDamayanListingNoticeData().getDataConvert() .convertAmount(getDamayanListingNoticeData().getGrandTotal())); cell.setCellStyle(boldStyle); } System.out.println("Total"); } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Damayan Listing Notice", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.println("damayanlistingnoticecontroller.export0 " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } } }