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 com.accounting.accountMBean; import com.accounting.entities.inventory.ISalesDetail; import com.accounting.entities.office.Office; import com.accounting.entities.user.User; import com.accounting.reportMBean.ReportMBean; import com.accounting.sessions.account.AccNatureSession; import com.accounting.sessions.user.AgentSessionBean; import com.accounting.utils.NepaliDateConverter; import com.accounting.utils.Util; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.annotation.PostConstruct; import javax.ejb.EJB; 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 org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; 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.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.util.CellRangeAddress; import org.primefaces.model.DefaultTreeNode; import org.primefaces.model.TreeNode; import com.accounting.commonutils.HelperUtil; import com.accounting.dto.AccountLadgerDTO; import com.accounting.dto.AgentsWiseCustomerDTO; import com.accounting.entities.account.AccMaster; import com.accounting.entities.account.AgentsInfo; import com.accounting.entities.user.FyTable; import com.accounting.jasperbean.JasperReportBean; import com.accounting.sessions.account.TxnDetailSession; import com.accounting.util.JsfUtil; import com.accounting.utils.KeyConstants; import com.accounting.utils.PageNameEnum; import java.util.HashMap; import java.util.Map; import java.util.logging.Level; import java.util.logging.Logger; import javax.faces.event.ActionEvent; import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @author User */ @ManagedBean @SessionScoped public class DifferentAccReports { @EJB AccNatureSession accNatureSession; @EJB AgentSessionBean agentSessionBean; @EJB TxnDetailSession tnxDetsailSession; private List<Object[]> accWiseList; private List<Object[]> bankAccountInfoList; private List<Object[]> creditList; private List<Object[]> salesCreditList; private List<Object[]> customerCreditList; private List<Object[]> individualCreditSalesDetail; private List<Object[]> agentsList; private List<Object[]> nonAgentList; private List<AgentsInfo> agentsMasterParentList; private List<ISalesDetail> individualCreditSalesDetailList; private List<AccountLadgerDTO> accountSummaryLsit; private List<AccountLadgerDTO> filterAccountSummaryLsit; private NepaliDateConverter ndc = new NepaliDateConverter(); private Date date = new Date(); private String toDate; private TreeNode root; String PageName; @ManagedProperty(value = "#{reportMBean}") private ReportMBean reportMBean; @ManagedProperty(value = "#{jasperReportBean}") JasperReportBean jasperReportBean; private String smartSearch, nepaliDate; private FyTable fyFromSession = new FyTable(); private int increaseDays; private String dr_cr; private int totalAmount; boolean headerText; boolean toggleForms; Double balannce, totalCr, totalDr; Object name, address, mobile, due_amount, bill_no, bill_amount, discount_amount, sold_date, expected_paymentDate, vat_percentage; @PostConstruct public void init() { headerText = true; fyFromSession = (FyTable) Util.getSession().getAttribute("fytable"); SimpleDateFormat simpleDate = new SimpleDateFormat("yyyy-MM-dd"); String startDateString = simpleDate.format(new Date()); String endDateString = simpleDate.format(new Date()); bankAccountInfoList = accNatureSession.retrieveAllBankAccountReport(getLoggedInOffice().getId()); creditList = new ArrayList<Object[]>(); creditList = accNatureSession.retrieveAllCreditSalesDetailsAcToDate(getLoggedInOffice().getId(), startDateString, endDateString); } public String gotoAccountReports(String reportType) { ndc = new NepaliDateConverter(); nepaliDate = ndc.convertToNepaliDate(new Date()); String returnPage = null; if (reportType.equals("supplier")) { HelperUtil.setPageName(PageNameEnum.SUPPLIERSACCOUNT); PageName = "Supplier Account Report"; returnPage = "supplierAccReport.xhtml?faces-redirect=true"; } else if (reportType.equals("customer")) { HelperUtil.setPageName(PageNameEnum.CUSTOMERSACCOUNT); PageName = "Customer Account Report"; returnPage = "customerAccReport.xhtml?faces-redirect=true"; } this.returnAccWiseReport(reportType); return returnPage; } String pageName; String buttonName; public String gotoDetailAccReports(String reportType) { ndc = new NepaliDateConverter(); nepaliDate = ndc.convertToNepaliDate(new Date()); String returnPage = null; if (reportType.equals("supplier")) { HelperUtil.setPageName(PageNameEnum.SUPPLIERSMANAGEMENT); pageName = "Supplier Account Management"; System.out.println("page name from templet is set as" + HelperUtil.getPageName()); buttonName = "Add Supplier Account"; } else if (reportType.equals("customer")) { HelperUtil.setPageName(PageNameEnum.CUSTOMERMANAGEMENT); pageName = "Customer Account Management"; buttonName = "Add Customer Account"; } this.returnAccWiseReport(reportType); return "customerAccReports.xhtml?faces-redirect=true"; } public String getButtonName() { return buttonName; } public void setButtonName(String buttonName) { this.buttonName = buttonName; } public String getPageName() { return pageName; } public void setPageName(String pageName) { this.pageName = pageName; } public String gotoBankAccountReports() { bankAccountInfoList = new ArrayList<Object[]>(); bankAccountInfoList = accNatureSession.retrieveAllBankAccountReport(getLoggedInOffice().getId()); PageName = "Bank Account Report"; return "bankAccountReport.xhtml?faces-redirect=true"; } // public String gotoAgeingReport() { // HelperUtil.setPageName(PageNameEnum.ARAGEINGREPORT); // PageName = "Ageing Report"; // toggleForms = false; // SimpleDateFormat simpleDate = new SimpleDateFormat("yyyy-MM-dd"); // String startDateString = simpleDate.format(new Date()); // String endDateString = simpleDate.format(new Date()); // creditList = new ArrayList<Object[]>(); // creditList = accNatureSession.retrieveAllCreditSalesDetailsAcToDate(getLoggedInOffice().getId(), startDateString, endDateString); // return "ageingReport.xhtml?faces-redirect=true"; // } public String gotoSalesAgeingReport() { PageName = "Sales Ageing Report"; toggleForms = false; showAllSalesAgeingReport(); return "salesAgeingReport.xhtml?faces-redirect=true"; } public String gotoSalesDueAgeingReport() { PageName = "Sales Due Ageing Report"; toggleForms = false; showAllDueSalesAgeingReport(); return "pastSalesAgeingReport.xhtml?faces-redirect=true"; } private List<AgentsWiseCustomerDTO> reportDTO; // public void basicSetupTreeTable() { // // SimpleDateFormat simpleDate = new SimpleDateFormat("yyyy-MM-dd"); // String startDateString = simpleDate.format(new Date()); // String endDateString = simpleDate.format(new Date()); // root = new DefaultTreeNode("root", null); // // root = new DefaultTreeNode("root", null); // // TreeNode age = null; // AgentsWiseCustomerDTO setupDTO3 = new AgentsWiseCustomerDTO("Agent"); // age = new DefaultTreeNode(setupDTO3, root); // age.setExpanded(true); // // agentsList = agentSessionBean.retrieveAllAgentsViaOfficeIdGroupByAgentName(HelperUtil.getLoggedInOfficeID(), startDateString); // TreeNode agentNode = null; // for (Object[] ic : agentsList) { // AgentsWiseCustomerDTO setupDTO1 = new AgentsWiseCustomerDTO(ic[0], ic[2]); // agentNode = new DefaultTreeNode(setupDTO1, age); // agentNode.setExpanded(true); // System.out.println("ajent id is" + ic[8]); // customerCreditList = accNatureSession.retrieveAllCreditSalesForAgeingDetailsAcToDate(HelperUtil.getLoggedInOfficeID(), startDateString, ic[8]); // // TreeNode customerChildNode = null; // for (Object[] ma : customerCreditList) { // System.out.println("ma[1] " + ma[1] + " ma[2] " + ma[2] + "ma[6]" + ma[6]); // // AgentsWiseCustomerDTO setupDTO2 = new AgentsWiseCustomerDTO(ma[1], ma[2], ma[3], ma[4], ma[5], ma[6], ma[7], ma[8], ma[9]); // customerChildNode = new DefaultTreeNode(setupDTO2, agentNode); // customerChildNode.setExpanded(false); // // } // // } // // nonAgentList = agentSessionBean.retriveAllCreditSalesWithOutAgent(HelperUtil.getLoggedInOfficeID(), startDateString); // // TreeNode nonAgentNode = null; // AgentsWiseCustomerDTO setupDTO4 = new AgentsWiseCustomerDTO("NonAgent", null, null, null, null, null, null, null, 0); // nonAgentNode = new DefaultTreeNode(setupDTO4, root); // nonAgentNode.setExpanded(true); // // TreeNode nonAgentChildNode = null; // for (Object[] non : nonAgentList) { // AgentsWiseCustomerDTO setupDTO = new AgentsWiseCustomerDTO(non[0], non[1], non[2], non[3], non[4], non[5], non[6], non[7], non[8]); // nonAgentChildNode = new DefaultTreeNode(setupDTO, nonAgentNode); // nonAgentChildNode.setExpanded(false); // } // // } public String expetedCollectionReportExcel(ActionEvent actionEvent) { Map params = new HashMap(); params.put("officeName", HelperUtil.getLoggedInOffice().getName()); params.put("currentDate", ndc.convertToNepaliDate(new Date())); // params.put("date1", asOfDate); // params.put("date2", previousDate); params.put("generatedBy", getLoggedInUser().getName()); try { JRBeanCollectionDataSource beancoldatasrc = new JRBeanCollectionDataSource(reportDTO); jasperReportBean.exportToExcel(actionEvent, beancoldatasrc, params, "expectedCollectionReportExcel"); } catch (Exception ex) { ex.printStackTrace(); } return "salesAgeingReport.xhtml"; } public String overDueCollectionReportExcel(ActionEvent actionEvent) { Map params = new HashMap(); params.put("officeName", HelperUtil.getLoggedInOffice().getName()); params.put("currentDate", ndc.convertToNepaliDate(new Date())); // params.put("date1", asOfDate); // params.put("date2", previousDate); params.put("generatedBy", getLoggedInUser().getName()); try { JRBeanCollectionDataSource beancoldatasrc = new JRBeanCollectionDataSource(reportDTO); jasperReportBean.exportToExcel(actionEvent, beancoldatasrc, params, "overDueCollectionReportExcel"); } catch (Exception ex) { ex.printStackTrace(); } return "pastSalesAgeingReport.xhtml"; } private List<AccMaster> partyList; public void returnAccWiseReport(String reportType) { totalCr = 0.0; totalDr = 0.0; partyList = new ArrayList<AccMaster>(); NepaliDateConverter n = new NepaliDateConverter(); int accOwnerId = 0; if (reportType.equals("supplier")) { HelperUtil.setPageName(PageNameEnum.SUPPLIERSMANAGEMENT); accOwnerId = KeyConstants.OWNERSUPPLIER; } else if (reportType.equals("customer")) { HelperUtil.setPageName(PageNameEnum.CUSTOMERMANAGEMENT); accOwnerId = KeyConstants.OWNERCUSTOMER; } accountSummaryLsit = new ArrayList<AccountLadgerDTO>(); accWiseList = new ArrayList<Object[]>(); // accWiseList = accNatureSession.retrieveAllAccWiseReport(accOwnerId, getLoggedInOffice().getId()); partyList = accNatureSession.partyAccountList(accOwnerId, getLoggedInOffice().getId()); for (int i = 0; i < partyList.size(); i++) { AccountLadgerDTO a = new AccountLadgerDTO(); a.setAccId(partyList.get(i).getId()); a.setName(partyList.get(i).getAccName()); try { a.setAddress(partyList.get(i).getPersonalInfo().getAddress()); a.setContact(partyList.get(i).getPersonalInfo().getMobile1()); a.setCreatedDate(partyList.get(i).getCreatedDate()); a.setPanNo(partyList.get(i).getPersonalInfo().getPanNo()); a.setOfficeNum(partyList.get(i).getPersonalInfo().getTelephoneO()); a.setResisNum(partyList.get(i).getPersonalInfo().getTelephoneR()); a.setCreditAction(partyList.get(i).getCreditAction()); a.setCreditLimit(partyList.get(i).getCreditLimit()); } catch (Exception e) { } try { // reportMBean.search(); Double balance = tnxDetsailSession.returnSumOfAcSearched(new Date(), partyList.get(i).getId(), n.convertToEnglishDate(fyFromSession.getStartDate())); if (balance < 0.0) { dr_cr = "D"; a.setDebitBalance(balance); totalDr += a.getDebitBalance(); } if (balance > 0.0) { dr_cr = "C"; a.setCretidBalance(balance); totalCr += a.getCretidBalance(); } } catch (Exception ex) { Logger.getLogger(DifferentAccReports.class.getName()).log(Level.SEVERE, null, ex); a.setBalance(0.0); } accountSummaryLsit.add(a); } } public void returnAccWiseReportSearchSuppliers() { totalDr = 0.0; totalCr = 0.0; partyList = new ArrayList<AccMaster>(); NepaliDateConverter n = new NepaliDateConverter(); int accOwnerId = 0; String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); System.out.println("to date is" + startDateString); Date start = ndc.convertToEnglishDate(startDateString); System.out.println("to date is" + start); HelperUtil.setPageName(PageNameEnum.SUPPLIERSACCOUNT); accOwnerId = KeyConstants.OWNERSUPPLIER; accountSummaryLsit = new ArrayList<AccountLadgerDTO>(); accWiseList = new ArrayList<Object[]>(); // accWiseList = accNatureSession.retrieveAllAccWiseReport(accOwnerId, getLoggedInOffice().getId()); partyList = accNatureSession.partyAccountList(accOwnerId, getLoggedInOffice().getId()); for (int i = 0; i < partyList.size(); i++) { AccountLadgerDTO a = new AccountLadgerDTO(); a.setAccId(partyList.get(i).getId()); a.setName(partyList.get(i).getAccName()); try { a.setAddress(partyList.get(i).getPersonalInfo().getAddress()); a.setContact(partyList.get(i).getPersonalInfo().getMobile1()); a.setCreatedDate(partyList.get(i).getCreatedDate()); a.setPanNo(partyList.get(i).getPersonalInfo().getPanNo()); a.setOfficeNum(partyList.get(i).getPersonalInfo().getTelephoneO()); a.setResisNum(partyList.get(i).getPersonalInfo().getTelephoneR()); a.setCreditAction(partyList.get(i).getCreditAction()); a.setCreditLimit(partyList.get(i).getCreditLimit()); } catch (Exception e) { } try { // reportMBean.search(); Double balance = tnxDetsailSession.retrieveOpeningBalancee(partyList.get(i).getId(), start, n.convertToEnglishDate(fyFromSession.getStartDate())); if (balance < 0.0) { dr_cr = "D"; a.setDebitBalance(balance); totalDr += a.getDebitBalance(); } if (balance >= 0.0) { dr_cr = "C"; a.setCretidBalance(balance); totalCr += a.getCretidBalance(); } } catch (ParseException ex) { Logger.getLogger(DifferentAccReports.class.getName()).log(Level.SEVERE, null, ex); a.setBalance(0.0); } accountSummaryLsit.add(a); } } public void returnAccWiseReportSearch() { totalCr = 0.0; totalDr = 0.0; partyList = new ArrayList<AccMaster>(); NepaliDateConverter n = new NepaliDateConverter(); int accOwnerId = 0; String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); System.out.println("to date is" + startDateString); Date start = ndc.convertToEnglishDate(startDateString); System.out.println("to date is" + start); HelperUtil.setPageName(PageNameEnum.CUSTOMERSACCOUNT); accOwnerId = KeyConstants.OWNERCUSTOMER; accountSummaryLsit = new ArrayList<AccountLadgerDTO>(); accWiseList = new ArrayList<Object[]>(); // accWiseList = accNatureSession.retrieveAllAccWiseReport(accOwnerId, getLoggedInOffice().getId()); partyList = accNatureSession.partyAccountList(accOwnerId, getLoggedInOffice().getId()); for (int i = 0; i < partyList.size(); i++) { AccountLadgerDTO a = new AccountLadgerDTO(); a.setAccId(partyList.get(i).getId()); a.setName(partyList.get(i).getAccName()); try { a.setAddress(partyList.get(i).getPersonalInfo().getAddress()); a.setContact(partyList.get(i).getPersonalInfo().getMobile1()); a.setCreatedDate(partyList.get(i).getCreatedDate()); a.setPanNo(partyList.get(i).getPersonalInfo().getPanNo()); a.setOfficeNum(partyList.get(i).getPersonalInfo().getTelephoneO()); a.setResisNum(partyList.get(i).getPersonalInfo().getTelephoneR()); a.setCreditAction(partyList.get(i).getCreditAction()); a.setCreditLimit(partyList.get(i).getCreditLimit()); } catch (Exception e) { } try { // reportMBean.search(); Double balance = tnxDetsailSession.returnSumOfAcSearched(start, partyList.get(i).getId(), n.convertToEnglishDate(fyFromSession.getStartDate())); if (balance < 0.0) { dr_cr = "D"; a.setDebitBalance(balance); totalDr += a.getDebitBalance(); } if (balance >= 0.0) { dr_cr = "C"; a.setCretidBalance(balance); totalCr += a.getCretidBalance(); } } catch (Exception ex) { Logger.getLogger(DifferentAccReports.class.getName()).log(Level.SEVERE, null, ex); a.setBalance(0.0); } accountSummaryLsit.add(a); } } public void returnBankAccountReport() { bankAccountInfoList = new ArrayList<Object[]>(); bankAccountInfoList = accNatureSession.retrieveAllBankAccountReport(getLoggedInOffice().getId()); } public String retrieveDetailsAboutCreditSale(int customer_id, int sales_id) { individualCreditSalesDetail = new ArrayList<Object[]>(); individualCreditSalesDetailList = new ArrayList<ISalesDetail>(); individualCreditSalesDetail = accNatureSession.retreiveAllInformationRegardingCreditSalesFromId(customer_id, sales_id, getLoggedInOffice().getId()); name = individualCreditSalesDetail.get(0)[5]; address = individualCreditSalesDetail.get(0)[2]; mobile = individualCreditSalesDetail.get(0)[4]; due_amount = individualCreditSalesDetail.get(0)[0]; bill_no = individualCreditSalesDetail.get(0)[6]; bill_amount = individualCreditSalesDetail.get(0)[7]; discount_amount = individualCreditSalesDetail.get(0)[12]; sold_date = individualCreditSalesDetail.get(0)[10]; expected_paymentDate = individualCreditSalesDetail.get(0)[1]; vat_percentage = individualCreditSalesDetail.get(0)[11]; individualCreditSalesDetailList = accNatureSession.retrieveCheck(sales_id, getLoggedInOffice().getId()); toggleForms = true; return "ageingReport.xhtml?faces-r3edirect=true"; } public void postProcessXLS(Object document) { String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate1"); ndc = new NepaliDateConverter(); HSSFWorkbook wb = (HSSFWorkbook) document; HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFCellStyle headerCellStyle1 = wb.createCellStyle(); HSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); HSSFSheet sheet = wb.getSheetAt(0); int noOfColumns = sheet.getRow(0).getLastCellNum(); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i); } sheet.shiftRows(0, sheet.getLastRowNum(), 4); HSSFRow firstRow = sheet.createRow(1); firstRow.createCell(0).setCellValue(PageName); firstRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow secondRow = sheet.createRow(0); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow thirdRow = sheet.createRow(3); String generatedDate = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + generatedDate + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); HSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("To Date: " + startDateString); fourthRow.getCell(0).setCellStyle(headerCellStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 4)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 4)); // HSSFRow lastRow; // double totalDr = 0; // for (Row row : sheet) { // if (row.getRowNum() > 4) { // String cost = row.getCell(3).getStringCellValue(); // if (cost != null && !cost.isEmpty()) { // row.getCell(3).setCellType(HSSFCell.CELL_TYPE_BLANK); // row.getCell(3).setCellType(HSSFCell.CELL_TYPE_NUMERIC); // row.getCell(3).setCellValue(Double.parseDouble(cost.replace(",", ""))); // totalDr += Double.parseDouble(cost.replace(",", "")); // } // } // } } public void postProcessXLSAgeingReport(Object document) { ndc = new NepaliDateConverter(); String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateReading = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate5"); HSSFWorkbook wb = (HSSFWorkbook) document; HSSFCellStyle headerCellStyle = wb.createCellStyle(); HSSFCellStyle headerCellStyle1 = wb.createCellStyle(); HSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); HSSFSheet sheet = wb.getSheetAt(0); int noOfColumns = sheet.getRow(0).getLastCellNum(); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i); } sheet.shiftRows(0, sheet.getLastRowNum(), 4); HSSFRow firstRow = sheet.createRow(1); firstRow.createCell(0).setCellValue(PageName); firstRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow secondRow = sheet.createRow(0); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow thirdRow = sheet.createRow(3); String generatedDate = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + generatedDate + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); HSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("From: " + startDateString + " To: " + endDateReading); fourthRow.getCell(0).setCellStyle(headerCellStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 6)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 6)); for (Row row : sheet) { if (row.getRowNum() > 4) { String cost = row.getCell(4).getStringCellValue(); if (cost != null && !cost.isEmpty()) { row.getCell(4).setCellType(HSSFCell.CELL_TYPE_BLANK); row.getCell(4).setCellType(HSSFCell.CELL_TYPE_NUMERIC); row.getCell(4).setCellValue(Double.parseDouble(cost.replace(",", ""))); } } } } // public void searchforSalesCreditButtonBetweenDate() { // ndc = new NepaliDateConverter(); // headerText = false; // String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap().get("nepaliDate4"); // String endDateReading = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap().get("nepaliDate5"); // if ("".equals(startDateString) || startDateString == null) { // showFacesMsg(FacesMessage.SEVERITY_ERROR, "Please Enter From Date", startDateString); // return; // } // if ("".equals(endDateReading) || endDateReading == null) { // showFacesMsg(FacesMessage.SEVERITY_ERROR, "Please Enter To Date", endDateReading); // return; // } // Date start = ndc.convertToEnglishDate(startDateString); // Date end = ndc.convertToEnglishDate(endDateReading); // SimpleDateFormat simpleDate = new SimpleDateFormat("yyyy-MM-dd"); // String dateStart = simpleDate.format(start); // String dateEnd = simpleDate.format(end); // root = new DefaultTreeNode("root", null); // // TreeNode age = null; // AgentsWiseCustomerDTO setupDTO3 = new AgentsWiseCustomerDTO("Agent"); // age = new DefaultTreeNode(setupDTO3, root); // age.setExpanded(true); // // agentsList = agentSessionBean.retrieveAllAgentsViaOfficeIdGroupByAgentName(HelperUtil.getLoggedInOfficeID(), dateEnd); // TreeNode agentNode = null; // for (Object[] ic : agentsList) { // AgentsWiseCustomerDTO setupDTO1 = new AgentsWiseCustomerDTO(ic[0], ic[2]); // agentNode = new DefaultTreeNode(setupDTO1, age); // agentNode.setExpanded(true); // customerCreditList = accNatureSession.retrieveAllCreditSalesForAgeingDetailsAcToDate(HelperUtil.getLoggedInOfficeID(), dateEnd, ic[9]); // // TreeNode customerChildNode = null; // for (Object[] ma : customerCreditList) { // // AgentsWiseCustomerDTO setupDTO2 = new AgentsWiseCustomerDTO(ma[1], ma[2], ma[3], ma[4], ma[5], ma[6], ma[7], ma[8], ma[9]); // customerChildNode = new DefaultTreeNode(setupDTO2, agentNode); // customerChildNode.setExpanded(false); // // } // // } // // nonAgentList = agentSessionBean.retriveAllCreditSalesWithOutAgent(HelperUtil.getLoggedInOfficeID(), dateEnd); // // TreeNode nonAgentNode = null; // AgentsWiseCustomerDTO setupDTO4 = new AgentsWiseCustomerDTO("NonAgent", null, null, null, null, null, null, null, 0); // nonAgentNode = new DefaultTreeNode(setupDTO4, root); // nonAgentNode.setExpanded(true); // // TreeNode nonAgentChildNode = null; // for (Object[] non : nonAgentList) { // AgentsWiseCustomerDTO setupDTO = new AgentsWiseCustomerDTO(non[0], non[1], non[2], non[3], non[4], non[5], non[6], non[7], non[8]); // nonAgentChildNode = new DefaultTreeNode(setupDTO, nonAgentNode); // nonAgentChildNode.setExpanded(false); // } // // } public void showAllSalesAgeingReport() { reportDTO = new ArrayList<AgentsWiseCustomerDTO>(); root = new DefaultTreeNode("root", null); TreeNode age = null; AgentsWiseCustomerDTO setupDTO3 = new AgentsWiseCustomerDTO("Agent", 1L); age = new DefaultTreeNode(setupDTO3, root); age.setExpanded(true); reportDTO.add(setupDTO3); agentsList = agentSessionBean.retrieveAllAgentsViaOfficeIdGroupByAgent(HelperUtil.getLoggedInOfficeID()); TreeNode agentNode = null; for (Object[] ic : agentsList) { double agentTotal = 0.0; List<AgentsWiseCustomerDTO> dto = new ArrayList<AgentsWiseCustomerDTO>(); customerCreditList = accNatureSession .retrieveAllCreditSalesForAgeingDetails(HelperUtil.getLoggedInOfficeID(), ic[8]); TreeNode customerChildNode = null; for (Object[] ma : customerCreditList) { AgentsWiseCustomerDTO setupDTO2 = new AgentsWiseCustomerDTO(ma[1], ma[3], ma[4], ma[5], ma[6], ma[7], ma[8], ma[9], 3L); reportDTO.add(setupDTO2); agentTotal += setupDTO2.getTotalAmount(); dto.add(setupDTO2); } AgentsWiseCustomerDTO setupDTO1 = new AgentsWiseCustomerDTO(ic[0], agentTotal, 2L); agentNode = new DefaultTreeNode(setupDTO1, age); agentNode.setExpanded(true); reportDTO.add(setupDTO1); for (AgentsWiseCustomerDTO ag : dto) { customerChildNode = new DefaultTreeNode(ag, agentNode); customerChildNode.setExpanded(false); } } nonAgentList = agentSessionBean.retriveAllCreditSalesWithOutAgents(HelperUtil.getLoggedInOfficeID()); TreeNode nonAgentNode = null; AgentsWiseCustomerDTO setupDTO4 = new AgentsWiseCustomerDTO("NonAgent", 1L); nonAgentNode = new DefaultTreeNode(setupDTO4, root); nonAgentNode.setExpanded(true); reportDTO.add(setupDTO4); TreeNode nonAgentChildNode = null; for (Object[] non : nonAgentList) { AgentsWiseCustomerDTO setupDTO = new AgentsWiseCustomerDTO(non[0], non[2], non[3], non[4], non[5], non[6], non[7], non[8], 2L); nonAgentChildNode = new DefaultTreeNode(setupDTO, nonAgentNode); nonAgentChildNode.setExpanded(false); reportDTO.add(setupDTO); } } public void showAllDueSalesAgeingReport() { reportDTO = new ArrayList<AgentsWiseCustomerDTO>(); root = new DefaultTreeNode("root", null); TreeNode age = null; AgentsWiseCustomerDTO setupDTO3 = new AgentsWiseCustomerDTO("Agent", 1L); age = new DefaultTreeNode(setupDTO3, root); age.setExpanded(true); reportDTO.add(setupDTO3); agentsList = agentSessionBean.retrieveAllAgentsViaOfficeIdGroupByAgent(HelperUtil.getLoggedInOfficeID()); TreeNode agentNode = null; for (Object[] ic : agentsList) { double agentTotal = 0.0; List<AgentsWiseCustomerDTO> dto = new ArrayList<AgentsWiseCustomerDTO>(); customerCreditList = accNatureSession .retrieveAllCreditSalesDueForAgeingDetails(HelperUtil.getLoggedInOfficeID(), ic[8]); TreeNode customerChildNode = null; for (Object[] ma : customerCreditList) { AgentsWiseCustomerDTO setupDTO2 = new AgentsWiseCustomerDTO(ma[1], ma[2], ma[3], ma[4], ma[5], ma[6], ma[7], ma[8], ma[9], 3L, ma[11]); reportDTO.add(setupDTO2); agentTotal += setupDTO2.getTotalAmount(); dto.add(setupDTO2); } AgentsWiseCustomerDTO setupDTO1 = new AgentsWiseCustomerDTO(ic[0], agentTotal, 2L); agentNode = new DefaultTreeNode(setupDTO1, age); agentNode.setExpanded(true); reportDTO.add(setupDTO1); for (AgentsWiseCustomerDTO ag : dto) { customerChildNode = new DefaultTreeNode(ag, agentNode); customerChildNode.setExpanded(false); } } nonAgentList = agentSessionBean.retriveAllCreditDueSalesWithOutAgents(HelperUtil.getLoggedInOfficeID()); TreeNode nonAgentNode = null; AgentsWiseCustomerDTO setupDTO4 = new AgentsWiseCustomerDTO("NonAgent", 1L); nonAgentNode = new DefaultTreeNode(setupDTO4, root); nonAgentNode.setExpanded(true); reportDTO.add(setupDTO4); TreeNode nonAgentChildNode = null; for (Object[] non : nonAgentList) { // System.out.println("ma[1] " + non[0] + " ma[2] " + non[2] + "ma[6]" + non[7]); AgentsWiseCustomerDTO setupDTO = new AgentsWiseCustomerDTO(non[0], non[1], non[2], non[3], non[4], non[5], non[6], non[7], non[8], 2L, non[10]); nonAgentChildNode = new DefaultTreeNode(setupDTO, nonAgentNode); nonAgentChildNode.setExpanded(false); reportDTO.add(setupDTO); } } public void simplePostProcessXLSX(Object document) { String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate5"); XSSFWorkbook wb = (XSSFWorkbook) document; XSSFCellStyle headerCellStyle = wb.createCellStyle(); XSSFCellStyle headerCellStyle1 = wb.createCellStyle(); XSSFCellStyle headerCellStyle2 = wb.createCellStyle(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); Font headerFont1 = wb.createFont(); headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING); headerFont1.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle1.setFont(headerFont); headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT); Font headerFont3 = wb.createFont(); headerFont3.setBoldweight(Font.U_SINGLE); headerFont3.setFontName(HSSFFont.FONT_ARIAL); headerCellStyle2.setFont(headerFont1); headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT); XSSFSheet sheet = wb.getSheetAt(0); int noOfColumns = sheet.getRow(2).getLastCellNum(); for (int i = 0; i < noOfColumns; i++) { sheet.autoSizeColumn(i); } sheet.shiftRows(0, sheet.getLastRowNum(), 4); XSSFRow firstRow = sheet.createRow(1); firstRow.createCell(0).setCellValue("SALES AGEING REPORT"); firstRow.getCell(0).setCellStyle(headerCellStyle); XSSFRow secondRow = sheet.createRow(0); secondRow.createCell(0).setCellValue(getLoggedInOffice().getName()); secondRow.getCell(0).setCellStyle(headerCellStyle); // XSSFRow thirdRow = sheet.createRow(3); String date = ndc.convertToNepaliDate(new Date()); SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a"); String time = sdf.format(new Date()); thirdRow.createCell(0) .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName()); thirdRow.getCell(0).setCellStyle(headerCellStyle2); XSSFRow fourthRow = sheet.createRow(2); fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString); fourthRow.getCell(0).setCellStyle(headerCellStyle); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7)); } public void searchButtonBetweenDate() { ndc = new NepaliDateConverter(); headerText = false; String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateReading = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate5"); if ("".equals(startDateString) || startDateString == null) { showFacesMsg(FacesMessage.SEVERITY_ERROR, "Please Enter From Date", endDateReading); return; } if ("".equals(endDateReading) || endDateReading == null) { showFacesMsg(FacesMessage.SEVERITY_ERROR, "Please Enter To Date", endDateReading); return; } Date start = ndc.convertToEnglishDate(startDateString); Date end = ndc.convertToEnglishDate(endDateReading); SimpleDateFormat simpleDate = new SimpleDateFormat("yyyy-MM-dd"); String dateStart = simpleDate.format(start); String dateEnd = simpleDate.format(end); creditList = accNatureSession.retrieveAllCreditSalesDetailsAcToDate(getLoggedInOffice().getId(), dateStart, dateEnd); if (creditList == null || creditList.isEmpty()) { JsfUtil.addErrorMessage("Sorry, no record found."); } } public void ajaxUpdateForExpiredAgeingReport(String searchOptions) { if (searchOptions.equals("showexpired")) { Date parsingDate = new Date(); System.out.println("The system date we have is " + parsingDate); Date myDate = accNatureSession.addDays(parsingDate, increaseDays); SimpleDateFormat simpleDate = new SimpleDateFormat("yyyy-MM-dd"); String dateStart = simpleDate.format(myDate); System.out.println("The number of increaing days become " + increaseDays); System.out.println("The date after conversion becomes " + dateStart); creditList = accNatureSession.retrieveAllCreditSalesDetailsForDates(getLoggedInOffice().getId(), dateStart); } else { creditList = accNatureSession.retrieveAllCreditSalesDetails(getLoggedInOffice().getId()); if (creditList == null || creditList.isEmpty()) { JsfUtil.addErrorMessage("Sorry, no record found."); } } } public String redirectToAccLedgerFrom(int incomingId) throws ParseException { System.out.println("The incoming id becomes " + incomingId); // HelperUtil.setPageName(PageNameEnum.CUSTOMERMANAGEMENT); reportMBean.id = incomingId; reportMBean.startDate = ndc.convertToEnglishDate(ndc.convertToNepaliDate(new Date())); reportMBean.endDate = ndc.convertToEnglishDate(ndc.convertToNepaliDate(new Date())); return reportMBean.search(); } public String redirectToAccLedgerFromCustomer(int incomingId) throws ParseException { System.out.println("The incoming id becomes " + incomingId); HelperUtil.setPageName(PageNameEnum.CUSTOMERMANAGEMENT); reportMBean.id = incomingId; reportMBean.startDate = ndc.convertToEnglishDate(ndc.convertToNepaliDate(new Date())); reportMBean.endDate = ndc.convertToEnglishDate(ndc.convertToNepaliDate(new Date())); return reportMBean.search(); } public String redirectToAccLedgerFromCustomerAccount(int incomingId) throws ParseException { System.out.println("The incoming id becomes " + incomingId); HelperUtil.setPageName(PageNameEnum.CUSTOMERSACCOUNT); reportMBean.id = incomingId; reportMBean.startDate = Util.convertFromDate(new Date()); reportMBean.endDate = Util.convertToDate(new Date()); return reportMBean.search(); } public String redirectToAccLedgerFromSupplier(int incomingId) throws ParseException { System.out.println("The incoming id becomes " + incomingId); HelperUtil.setPageName(PageNameEnum.SUPPLIERSACCOUNT); reportMBean.id = incomingId; reportMBean.startDate = Util.convertFromDate(new Date()); reportMBean.endDate = Util.convertToDate(new Date()); return reportMBean.search(); } public Office getLoggedInOffice() { Office o = (Office) Util.getSession().getAttribute("office"); return o; } public User getLoggedInUser() { User o = (User) Util.getSession().getAttribute("user"); return o; } public void showFacesMsg(FacesMessage.Severity severType, String first, String second) { FacesMessage msg = new FacesMessage(severType, first, second); FacesContext context = FacesContext.getCurrentInstance(); context.addMessage(null, msg); } public void backButtonToggleMainForm() { toggleForms = false; } public List<Object[]> getAccWiseList() { return accWiseList; } public void setAccWiseList(List<Object[]> accWiseList) { this.accWiseList = accWiseList; } public List<Object[]> getBankAccountInfoList() { return bankAccountInfoList; } public void setBankAccountInfoList(List<Object[]> bankAccountInfoList) { this.bankAccountInfoList = bankAccountInfoList; } public AccNatureSession getAccNatureSession() { return accNatureSession; } public void setAccNatureSession(AccNatureSession accNatureSession) { this.accNatureSession = accNatureSession; } public ReportMBean getReportMBean() { return reportMBean; } public void setReportMBean(ReportMBean reportMBean) { this.reportMBean = reportMBean; } public List<Object[]> getCreditList() { return creditList; } public void setCreditList(List<Object[]> creditList) { this.creditList = creditList; } public Date getDate() { return date; } public void setDate(Date date) { this.date = date; } public String getSmartSearch() { return smartSearch; } public void setSmartSearch(String smartSearch) { this.smartSearch = smartSearch; } public boolean isHeaderText() { return headerText; } public void setHeaderText(boolean headerText) { this.headerText = headerText; } public List<Object[]> getIndividualCreditSalesDetail() { return individualCreditSalesDetail; } public void setIndividualCreditSalesDetail(List<Object[]> individualCreditSalesDetail) { this.individualCreditSalesDetail = individualCreditSalesDetail; } public List<ISalesDetail> getIndividualCreditSalesDetailList() { return individualCreditSalesDetailList; } public void setIndividualCreditSalesDetailList(List<ISalesDetail> individualCreditSalesDetailList) { this.individualCreditSalesDetailList = individualCreditSalesDetailList; } public Object getName() { return name; } public void setName(Object name) { this.name = name; } public Object getAddress() { return address; } public void setAddress(Object address) { this.address = address; } public Object getMobile() { return mobile; } public void setMobile(Object mobile) { this.mobile = mobile; } public Object getDue_amount() { return due_amount; } public void setDue_amount(Object due_amount) { this.due_amount = due_amount; } public Object getBill_no() { return bill_no; } public void setBill_no(Object bill_no) { this.bill_no = bill_no; } public Object getBill_amount() { return bill_amount; } public String getNepaliDate() { return nepaliDate; } public void setNepaliDate(String nepaliDate) { this.nepaliDate = nepaliDate; } public void setBill_amount(Object bill_amount) { this.bill_amount = bill_amount; } public Object getDiscount_amount() { return discount_amount; } public void setDiscount_amount(Object discount_amount) { this.discount_amount = discount_amount; } public Object getSold_date() { return sold_date; } public void setSold_date(Object sold_date) { this.sold_date = sold_date; } public Object getExpected_paymentDate() { return expected_paymentDate; } public void setExpected_paymentDate(Object expected_paymentDate) { this.expected_paymentDate = expected_paymentDate; } public Object getVat_percentage() { return vat_percentage; } public void setVat_percentage(Object vat_percentage) { this.vat_percentage = vat_percentage; } public boolean isToggleForms() { return toggleForms; } public void setToggleForms(boolean toggleForms) { this.toggleForms = toggleForms; } public int getTotalAmount() { return totalAmount; } public void setTotalAmount(int totalAmount) { this.totalAmount = totalAmount; } public int getIncreaseDays() { return increaseDays; } public void setIncreaseDays(int increaseDays) { this.increaseDays = increaseDays; } public List<Object[]> getSalesCreditList() { return salesCreditList; } public void setSalesCreditList(List<Object[]> salesCreditList) { this.salesCreditList = salesCreditList; } public List<Object[]> getCustomerCreditList() { return customerCreditList; } public void setCustomerCreditList(List<Object[]> customerCreditList) { this.customerCreditList = customerCreditList; } public TreeNode getRoota() { return root; } public void setRoota(TreeNode root) { this.root = root; } public List<AgentsInfo> getAgentsMasterParentList() { return agentsMasterParentList; } public void setAgentsMasterParentList(List<AgentsInfo> agentsMasterParentList) { this.agentsMasterParentList = agentsMasterParentList; } public TreeNode getRoot() { return root; } public void setRoot(TreeNode root) { this.root = root; } public Double getBalannce() { return balannce; } public void setBalannce(Double balannce) { this.balannce = balannce; } public List<AccountLadgerDTO> getAccountSummaryLsit() { return accountSummaryLsit; } public void setAccountSummaryLsit(List<AccountLadgerDTO> accountSummaryLsit) { this.accountSummaryLsit = accountSummaryLsit; } public List<AccMaster> getPartyList() { return partyList; } public void setPartyList(List<AccMaster> partyList) { this.partyList = partyList; } public FyTable getFyFromSession() { return fyFromSession; } public void setFyFromSession(FyTable fyFromSession) { this.fyFromSession = fyFromSession; } public String getCurrentDate() { String currentDate = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate1"); if (currentDate == null || currentDate.isEmpty()) { currentDate = ndc.convertToNepaliDate(new Date()); } return currentDate; } public List<AccountLadgerDTO> getFilterAccountSummaryLsit() { return filterAccountSummaryLsit; } public void setFilterAccountSummaryLsit(List<AccountLadgerDTO> filterAccountSummaryLsit) { this.filterAccountSummaryLsit = filterAccountSummaryLsit; } public String getDr_cr() { return dr_cr; } public void setDr_cr(String dr_cr) { this.dr_cr = dr_cr; } public JasperReportBean getJasperReportBean() { return jasperReportBean; } public void setJasperReportBean(JasperReportBean jasperReportBean) { this.jasperReportBean = jasperReportBean; } public Double getTotalCr() { return totalCr; } public void setTotalCr(Double totalCr) { this.totalCr = totalCr; } public Double getTotalDr() { return totalDr; } public void setTotalDr(Double totalDr) { this.totalDr = totalDr; } }