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.ArrayList; import java.util.Date; import java.util.List; import javax.faces.application.FacesMessage; import javax.faces.bean.ManagedBean; import javax.faces.bean.ManagedProperty; import javax.faces.bean.SessionScoped; import javax.faces.context.FacesContext; //import model.ClassRiSummary; //import class_summary.ClassSummary; 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 @SessionScoped public class ClassRiData implements Serializable { /** * Creates a new instance of ClassRiData */ public ClassRiData() { } /* * properties */ @ManagedProperty(value = "#{customEntityManagerFactory}") private CustomEntityManagerFactory customEntityManagerFactory; @ManagedProperty(value = "#{accountsWithSubsidiaryData}") private AccountsWithSubsidiaryData accountsWithSubsidiaryData; @ManagedProperty(value = "#{printController}") private PrintController printController; @ManagedProperty(value = "#{customDate}") private CustomDate customDate; @ManagedProperty(value = "#{exportData}") private ExportData exportData; @ManagedProperty(value = "#{dataConvert}") private DataConvert dataConvert; private List<List<Object[]>> classRiSummary; private List<Object[]> classRiSummaryPrint; /* * getter setter */ public CustomEntityManagerFactory getCustomEntityManagerFactory() { return customEntityManagerFactory == null ? customEntityManagerFactory = new CustomEntityManagerFactory() : customEntityManagerFactory; } public void setCustomEntityManagerFactory(CustomEntityManagerFactory customEntityManagerFactory) { this.customEntityManagerFactory = customEntityManagerFactory; } public AccountsWithSubsidiaryData getAccountsWithSubsidiaryData() { return accountsWithSubsidiaryData == null ? accountsWithSubsidiaryData = new AccountsWithSubsidiaryData() : accountsWithSubsidiaryData; } public void setAccountsWithSubsidiaryData(AccountsWithSubsidiaryData accountsWithSubsidiaryData) { this.accountsWithSubsidiaryData = accountsWithSubsidiaryData; } public PrintController getPrintController() { return printController == null ? printController = new PrintController() : printController; } public void setPrintController(PrintController printController) { this.printController = printController; } public CustomDate getCustomDate() { return customDate == null ? customDate = new CustomDate() : customDate; } public void setCustomDate(CustomDate customDate) { this.customDate = customDate; } public ExportData getExportData() { return exportData == null ? exportData = new ExportData() : exportData; } public void setExportData(ExportData exportData) { this.exportData = exportData; } public DataConvert getDataConvert() { return dataConvert == null ? dataConvert = new DataConvert() : dataConvert; } public void setDataConvert(DataConvert dataConvert) { this.dataConvert = dataConvert; } public List<List<Object[]>> getClassRiSummary() { return classRiSummary == null ? classRiSummary = new ArrayList<>() : classRiSummary; } public void setClassRiSummary(List<List<Object[]>> classRiSummary) { this.classRiSummary = classRiSummary; } public List<Object[]> getClassRiSummaryPrint() { return classRiSummaryPrint == null ? classRiSummaryPrint = new ArrayList<>() : classRiSummaryPrint; } public void setClassRiSummaryPrint(List<Object[]> classRiSummaryPrint) { this.classRiSummaryPrint = classRiSummaryPrint; } /* * methods */ public void init() { String query; if (FacesContext.getCurrentInstance().isPostback() == false) { beanclear(); getAccountsWithSubsidiaryData().beanclear(); } query = "SELECT coa.accountCode FROM CoopFinChartOfAccounts coa WHERE coa.accountCode LIKE :accountCode AND coa.level = 5 ORDER BY coa.accountCode"; getAccountsWithSubsidiaryData().setAccountCodes( getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory().createEntityManager() .createQuery(query).setParameter("accountCode", "441503%").getResultList()); getAccountsWithSubsidiaryData().setAccountCodesInclude(null); for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodes().size(); i++) { getAccountsWithSubsidiaryData().getAccountCodesInclude().add(i, false); } } public void beanclear() { FacesContext.getCurrentInstance().getExternalContext().getSessionMap().put("classRiData", null); } public String queryGenerator(String accountCodeParam, String selectedBatchParam) { String query; query = "SELECT x.row_number, " + "x.slNo, " + "x.acctno, " + "x.accountCode, " + "x.acctName, " + "x.balance, " + "x.acctStatus, " + "x.acctCreateDate, " + "x.postDate " + "FROM (SELECT row_number() OVER () AS row_number, " + "cls.slNo, " + "cls.acctno, " + "cls.accountCode, " + "cls.acctName, " + "cls.balance, " + "cls.acctStatus, " + "cls.acctCreateDate, " + "cls.postDate " + "FROM (SELECT DISTINCT ON (apd.acctno) sl.ri_sl_no AS slNo, " + "apd.acctno AS acctno, " + "apd.account_code AS accountCode, " + "cls.acct_name AS acctName, " + "sl.balance AS balance, " + "apd.acct_status AS acctStatus, " + "apd.acct_create_date AS acctCreateDate, " + "sl.post_date AS postDate " + "FROM coop_fin_acct_profile_dtl apd " + "JOIN coop_fin_class_ri cls ON apd.acctno::text = cls.acctno::text " + "JOIN coop_fin_ri_sl sl ON cls.acctno::text = sl.acctno::text "; if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null && getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) { query += "WHERE apd.acct_create_date BETWEEN '" + getCustomDate().formatDate(getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "yyyy-MM-dd") + "' AND '" + getCustomDate().formatDate(getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "yyyy-MM-dd") + "' "; } else if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null && getAccountsWithSubsidiaryData().getAcctCreateDateTo() == null) { query += "WHERE apd.acct_create_date BETWEEN '" + getCustomDate().formatDate(getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "yyyy-MM-dd") + "' AND '" + getCustomDate().formatDate(getCustomDate().getCurrentDate(), "yyyy-MM-dd") + "' "; } else if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() == null && getAccountsWithSubsidiaryData().getAcctCreateDateTo() == null) { query += "WHERE apd.acct_create_date BETWEEN '1900-01-01' AND '" + getCustomDate().formatDate(getCustomDate().getCurrentDate(), "yyyy-MM-dd") + "' "; } if (getAccountsWithSubsidiaryData().getReportDate() == null) { query += "AND sl.post_date <= '" + getCustomDate().formatDate(getCustomDate().getCurrentDate(), "yyyy-MM-dd") + "' "; } else { query += "AND sl.post_date <= '" + getCustomDate().formatDate(getAccountsWithSubsidiaryData().getReportDate(), "yyyy-MM-dd") + "' "; } query += "AND apd.account_code = '" + accountCodeParam + "' "; if (getAccountsWithSubsidiaryData().getAmountFilter() != null && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1) { query += "AND sl.balance <= " + getAccountsWithSubsidiaryData().getAmountFilter(); } // new code below // query += "AND (apd.acct_status <> 'C') " // + "OR (apd.acct_status = 'C' " // + "AND sl.balance <> 0) "; // new code above query += " ORDER BY apd.acctno, sl.post_date DESC, sl.ri_sl_no DESC) cls " + "WHERE (cls.acctStatus = 'A') " + "OR (cls.acctStatus = 'C' " + "AND cls.balance <> 0) " + "OR (cls.acctStatus = 'O' " + "AND cls.balance <> 0) " + "ORDER BY cls.acctno) x "; if (!selectedBatchParam.equals("ALL")) { query += "WHERE x. row_number " + selectedBatchParam; } return query; } public void runReport() { getAccountsWithSubsidiaryData().setSelectedAccountCode(""); getAccountsWithSubsidiaryData().setAccountCodesFiltered(null); getAccountsWithSubsidiaryData().setNoOfRows(null); getAccountsWithSubsidiaryData().setGrandTotal(BigDecimal.ZERO); if (getAccountsWithSubsidiaryData().getAccountCodesInclude().contains(true)) { for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesInclude().size(); i++) { if (getAccountsWithSubsidiaryData().getAccountCodesInclude().get(i)) { getAccountsWithSubsidiaryData().getAccountCodesFiltered() .add(getAccountsWithSubsidiaryData().getAccountCodes().get(i)); } } } else { getAccountsWithSubsidiaryData() .setAccountCodesFiltered(getAccountsWithSubsidiaryData().getAccountCodes()); } computeTotal(); for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) { getClassRiSummary().add(i, getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory().createEntityManager() .createNativeQuery(queryGenerator( getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i), "ALL")) .getResultList()); } getAccountsWithSubsidiaryData().setRunComplete(true); FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(FacesMessage.SEVERITY_INFO, "Run complete", "")); } public void accountCodeMe() { getAccountsWithSubsidiaryData().setSubtotalPrint(BigDecimal.ZERO); setClassRiSummaryPrint(getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory() .createEntityManager() .createNativeQuery(queryGenerator(getAccountsWithSubsidiaryData().getSelectedAccountCode(), "ALL")) .getResultList()); getAccountsWithSubsidiaryData().setNoOfRowsPrint(getClassRiSummaryPrint().size()); for (int i = 0; i < getClassRiSummaryPrint().size(); i++) { getAccountsWithSubsidiaryData().setSubtotalPrint(getAccountsWithSubsidiaryData().getSubtotalPrint() .add(((BigDecimal) getClassRiSummaryPrint().get(i)[5]))); } getPrintController().method0(getClassRiSummaryPrint().size()); } public void batchMe() { try { setClassRiSummaryPrint(getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory() .createEntityManager() .createNativeQuery(queryGenerator(getAccountsWithSubsidiaryData().getSelectedAccountCode(), getPrintController().getSelectedBatch())) .getResultList()); } catch (Exception e) { } } public void computeTotal() { String query; for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) { query = "FROM (SELECT DISTINCT ON (apd.acctno) sl.ri_sl_no AS slNo, " + "apd.acctno AS acctno, " + "apd.account_code AS accountCode, " + "cls.acct_name AS acctName, " + "sl.balance AS balance, " + "apd.acct_create_date AS acctCreateDate, " // + "sl.post_date AS postDate " // new code below + "sl.post_date AS postDate, " + "apd.acct_status AS acctStatus " // new code above + "FROM coop_fin_acct_profile_dtl apd " + "JOIN coop_fin_class_ri cls ON apd.acctno::text = cls.acctno::text " + "JOIN coop_fin_ri_sl sl ON cls.acctno::text = sl.acctno::text "; if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null && getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) { query += "WHERE apd.acct_create_date BETWEEN '" + getCustomDate().formatDate(getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "yyyy-MM-dd") + "' AND '" + getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "yyyy-MM-dd") + "' "; } else if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null && getAccountsWithSubsidiaryData().getAcctCreateDateTo() == null) { query += "WHERE apd.acct_create_date BETWEEN '" + getCustomDate().formatDate(getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "yyyy-MM-dd") + "' AND '" + getCustomDate().formatDate(getCustomDate().getCurrentDate(), "yyyy-MM-dd") + "' "; } else if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() == null && getAccountsWithSubsidiaryData().getAcctCreateDateTo() == null) { query += "WHERE apd.acct_create_date BETWEEN '1900-01-01' AND '" + getCustomDate().formatDate(getCustomDate().getCurrentDate(), "yyyy-MM-dd") + "' "; } if (getAccountsWithSubsidiaryData().getReportDate() == null) { query += "AND sl.post_date <= '" + getCustomDate().formatDate(getCustomDate().getCurrentDate(), "yyyy-MM-dd") + "' "; } else { query += "AND sl.post_date <= '" + getCustomDate().formatDate(getAccountsWithSubsidiaryData().getReportDate(), "yyyy-MM-dd") + "' "; } query += "AND apd.account_code = '" + getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i) + "' "; if (getAccountsWithSubsidiaryData().getAmountFilter() != null && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1) { query += "AND sl.balance <= " + getAccountsWithSubsidiaryData().getAmountFilter(); } // new code below // query += "AND (apd.acct_status <> 'C') " // + "OR (apd.acct_status = 'C' " // + "AND sl.balance <> 0) "; // new code above query += " ORDER BY apd.acctno, sl.post_date DESC, sl.ri_sl_no DESC) cls " + "WHERE (cls.acctStatus = 'A') " + "OR (cls.acctStatus = 'C' " + "AND cls.balance <> 0) " + "OR (cls.acctStatus = 'O' " + "AND cls.balance <> 0) "; // + "ORDER BY cls.acctno"; getAccountsWithSubsidiaryData().getNoOfRows().add(i, (Long) getCustomEntityManagerFactory().getFinancialDbEntityManagerFactory() .createEntityManager().createNativeQuery("SELECT COUNT(cls) " + query).getResultList() .get(0)); getAccountsWithSubsidiaryData().getSubtotal().add(i, (BigDecimal) getCustomEntityManagerFactory() .getFinancialDbEntityManagerFactory().createEntityManager() .createNativeQuery("SELECT COALESCE(SUM(cls.balance), 0) " + query).getResultList().get(0)); getAccountsWithSubsidiaryData().setGrandTotal(getAccountsWithSubsidiaryData().getGrandTotal() .add(getAccountsWithSubsidiaryData().getSubtotal().get(i))); } } public void export0() { Integer columnNo; HSSFWorkbook workbook; HSSFSheet sheet; HSSFRow headerRow, dataRow, totalRow = null; HSSFCell cell; HSSFCellStyle cellStyle, boldStyle; HSSFFont font; ThemeDisplay themeDisplay = LiferayFacesContext.getInstance().getThemeDisplay(); getExportData().createFolder(null, themeDisplay, "Rental Income SL Report", "DESCRIPTION"); 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); for (int i = 0; i < getAccountsWithSubsidiaryData().getAccountCodesFiltered().size(); i++) { try { sheet = workbook.createSheet(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); } catch (Exception e) { sheet = workbook.createSheet(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i)); } headerRow = sheet.createRow((short) 0); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getDataConvert() .accountCodeConvert(getAccountsWithSubsidiaryData().getAccountCodesFiltered().get(i))); cell.setCellStyle(boldStyle); try { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } catch (Exception e) { getAccountsWithSubsidiaryData().setReportDate(getCustomDate().getCurrentDate()); cell.setCellValue("As of " + getCustomDate() .formatDate(getAccountsWithSubsidiaryData().getReportDate(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if (getAccountsWithSubsidiaryData().getAcctCreateDateFrom() != null || getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue(getAccountsWithSubsidiaryData().getAcctCreateDateTo() != null ? "Account Created Date: " + getCustomDate() .formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY") .concat(" - ") .concat(getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateTo(), "MM-dd-YYYY")) : "Account Created Date: " + getCustomDate().formatDate( getAccountsWithSubsidiaryData().getAcctCreateDateFrom(), "MM-dd-YYYY")); cell.setCellStyle(boldStyle); } if ((getAccountsWithSubsidiaryData().getAmountFilter() != null && getAccountsWithSubsidiaryData().getAmountFilter().compareTo(BigDecimal.ZERO) == 1)) { headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Amount Range: " + getDataConvert() .numericConvert(getAccountsWithSubsidiaryData().getAmountFilter().doubleValue())); cell.setCellStyle(boldStyle); } headerRow = sheet.createRow(headerRow.getRowNum() + 1); headerRow = sheet.createRow(headerRow.getRowNum() + 1); columnNo = 0; cell = headerRow.createCell(columnNo++); cell.setCellValue("Account No."); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Name"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Account Status"); cell.setCellStyle(boldStyle); cell = headerRow.createCell(columnNo++); cell.setCellValue("Balance"); cell.setCellStyle(boldStyle); for (int ii = 0; ii < getClassRiSummary().get(i).size(); ii++) { columnNo = 0; dataRow = sheet.createRow(headerRow.getRowNum() + ii + 1); dataRow.createCell(columnNo++).setCellValue(getClassRiSummary().get(i).get(ii)[2].toString()); dataRow.createCell(columnNo++).setCellValue(getClassRiSummary().get(i).get(ii)[4].toString()); dataRow.createCell(columnNo++).setCellValue(getDataConvert() .acctStatusConvert(getClassRiSummary().get(i).get(ii)[6].toString().charAt(0))); cell = dataRow.createCell(columnNo++); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((BigDecimal) getClassRiSummary().get(i).get(ii)[5]).doubleValue()); cell.setCellStyle(cellStyle); totalRow = sheet.createRow((short) dataRow.getRowNum() + 2); } if (getClassRiSummary().get(i).size() > 0) { cell = totalRow.createCell(1); cell.setCellValue("TOTAL"); cell.setCellStyle(boldStyle); cell = totalRow.createCell(2); cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(getAccountsWithSubsidiaryData().getSubtotal().get(i).doubleValue()); cell.setCellStyle(cellStyle); } } FileOutputStream fileOutputStream = new FileOutputStream(getExportData().getFilename()); workbook.write(fileOutputStream); fileOutputStream.close(); getExportData().fileUploadByDL(getExportData().getFilename(), "Rental Income SL Report", themeDisplay, null); File file = new File(getExportData().getFilename()); if (file.exists()) { file.delete(); } } catch (Exception e) { System.out.print("classRiData().export0() " + e); FacesMessage message = new FacesMessage(FacesMessage.SEVERITY_ERROR, "Error", "An error occurred while generating excel file."); FacesContext.getCurrentInstance().addMessage(null, message); } } }