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.reportMBean; import com.accounting.dto.PurchasSalesDTO; import com.accounting.entities.inventory.IItemMaster; import com.accounting.entities.inventory.ISalesDetail; import com.accounting.entities.office.Office; import com.accounting.entities.user.User; import com.accounting.sessions.inventory.ItemMasterSession; 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.Collections; import java.util.Comparator; import java.util.Date; import java.util.List; import javax.annotation.PostConstruct; import javax.ejb.EJB; import javax.faces.bean.ManagedBean; 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.context.RequestContext; /** * * @author Asgar */ @ManagedBean @SessionScoped public class StockLedgerMBeans { @EJB ItemMasterSession itemMasterSession; private Date dateStart; private Date dateEnd; private Date startDate; private Date endDate; private List<IItemMaster> itemId; private String currentDate; private NepaliDateConverter nepaliDateConverter = new NepaliDateConverter(); private List<IItemMaster> itemsList = new ArrayList<IItemMaster>(); private List<IItemMaster> itemsListRaw = new ArrayList<IItemMaster>(); private List<IItemMaster> itemsListFinishedGood = new ArrayList<IItemMaster>(); public List<ISalesDetail> individualCreditSalesDetailList; private List<Object> stockSaleBetweenDate; private List<Object> itemIssueBetweenDate; private List<Object> purchaseReturnBetweenDates; private List<Object> salesReturnBetweenDates; private List<Object> stockPurchaseBetweenDate; List<PurchasSalesDTO> stockLedgerInquiryList; private Long[] selectedItems; boolean showRefresh, showPrchaseIssueDatatable; public List<Object[]> salesDetail; public List<Object[]> purchaseDetail; private NepaliDateConverter ndc = new NepaliDateConverter(); Object itemname, Sid, shdId, sQty, SDateNepali, SAccName, SP, salesDate, SunitPrice, SBilll, SbillAmount, disReason, paymentMethd, VAT, SAccNo, SaccOpen; Object purchaseQty, unitPrice, mrp, purchaseDateNepali, name, purchaseDate, bill, id, refNo, accNumber, wareHouse, location; int count; int cellCount; private String reportHeader; double sumPurchaseQty, sumPurchaseAmount, sumSoldQtyy, sumSoldQty, sumAvaviableQty, sumAvaviableAmount, sumSoldAmount; double sumPurchaseRetQty, sumPurchaseRetAmount, sumSalesRetQty, sumSalesRetAmount; public String gotoStockLedger() { resetSum(); showRefresh = false; selectedItems = new Long[itemsList.size()]; itemId = new ArrayList<IItemMaster>(); stockSaleBetweenDate = new ArrayList<Object>(); stockLedgerInquiryList = new ArrayList<PurchasSalesDTO>(); count = 10; cellCount = 9; reportHeader = "Stock Ledger With Values"; return "stockLedgerWithValues.xhtml?faces-redirect=true"; } public void resetSum() { sumAvaviableAmount = 0.0; sumAvaviableQty = 0.0; sumPurchaseAmount = 0.0; sumPurchaseQty = 0.0; // sumSoldQty=0.0; sumSoldQtyy = 0.0; sumSoldAmount = 0.0; sumPurchaseRetAmount = 0.0; sumPurchaseRetQty = 0.0; sumSalesRetQty = 0.0; sumSalesRetAmount = 0.0; } public String gotoPurchaseIssueReport() { resetSum(); showRefresh = false; selectedItems = new Long[itemsList.size()]; itemId = new ArrayList<IItemMaster>(); stockSaleBetweenDate = new ArrayList<Object>(); itemIssueBetweenDate = new ArrayList<Object>(); stockLedgerInquiryList = new ArrayList<PurchasSalesDTO>(); count = 7; cellCount = 6; reportHeader = "Stock Detail Report(Raw Material)"; showPrchaseIssueDatatable = false; return "reportpurchaseIssue.xhtml?faces-redirect=true"; } public String gotoFinishedGoodReport() { resetSum(); stockSaleBetweenDate = new ArrayList<Object>(); itemIssueBetweenDate = new ArrayList<Object>(); stockLedgerInquiryList = new ArrayList<PurchasSalesDTO>(); count = 7; cellCount = 6; showPrchaseIssueDatatable = false; reportHeader = "Stock Detail Report(Finished Goods)"; return "reportFinishedGood.xhtml?faces-redirect=true"; } @PostConstruct public void init() { ndc = new NepaliDateConverter(); stockLedgerInquiryList = new ArrayList<PurchasSalesDTO>(); stockPurchaseBetweenDate = new ArrayList<Object>(); NepaliDateConverter ndc = new NepaliDateConverter(); itemId = new ArrayList<IItemMaster>(); currentDate = ndc.convertToNepaliDate(new Date()); dateStart = new Date(); dateEnd = new Date(); retrieveAllItemsByOfficeId(); retrieveAllRawItemsByOfficeId(); retrieveAllFinishedItemsByOfficeId(); selectedItems = new Long[itemsList.size()]; // retrieveAllStockBetweenDate(); } public void retrieveAllItemsByOfficeId() { itemsList = new ArrayList<IItemMaster>(); itemsList = itemMasterSession.returnAllItemsByOffice(getLoggedInOffice().getId()); } public void retrieveAllRawItemsByOfficeId() { itemsListRaw = new ArrayList<IItemMaster>(); itemsListRaw = itemMasterSession.returnAllRawItemsByOffice(getLoggedInOffice().getId()); } public void retrieveAllFinishedItemsByOfficeId() { itemsListFinishedGood = new ArrayList<IItemMaster>(); itemsListFinishedGood = itemMasterSession.returnAllFinishedItemsByOffice(getLoggedInOffice().getId()); } public void showFurtherDetail(Long a, Date d, String type, String bill_no, long shId) { if (type.equals("P")) { this.retrieveDetailsAboutPurchase(a, d, bill_no, shId); } else if (type.equals("S")) { retrieveDetailsAboutSale(a, d, bill_no, shId); } this.showDetailsPopUp(type); } public String retrieveDetailsAboutPurchase(Long id, Date date, String bill_no, Long shId) { purchaseDetail = new ArrayList<Object[]>(); purchaseDetail = itemMasterSession.retreiveAllInformationPurchase(id, date, getLoggedInOffice().getId(), bill_no, shId); purchaseQty = purchaseDetail.get(0)[0]; unitPrice = purchaseDetail.get(0)[1]; mrp = purchaseDetail.get(0)[2]; purchaseDateNepali = purchaseDetail.get(0)[3]; name = purchaseDetail.get(0)[12]; purchaseDate = purchaseDetail.get(0)[5]; bill = purchaseDetail.get(0)[6]; id = Long.valueOf(purchaseDetail.get(0)[7].toString()); refNo = purchaseDetail.get(0)[8]; accNumber = purchaseDetail.get(0)[9]; wareHouse = purchaseDetail.get(0)[10]; location = purchaseDetail.get(0)[11]; // individualCreditSalesDetailList = accNatureSession.retrieveCheck(sales_id, getLoggedInOffice().getId()); return "stockLedgerWithValues.xhtml?faces-redirect=true"; } public String retrieveDetailsAboutSale(Long id, Date date, String bill_no, Long shId) { salesDetail = new ArrayList<Object[]>(); salesDetail = itemMasterSession.retreiveAllInformationSales(id, date, getLoggedInOffice().getId(), bill_no, shId); if (salesDetail != null && !salesDetail.isEmpty()) { itemname = salesDetail.get(0)[0]; Sid = salesDetail.get(0)[1]; shdId = salesDetail.get(0)[2]; sQty = salesDetail.get(0)[3]; SDateNepali = salesDetail.get(0)[4]; SAccName = salesDetail.get(0)[5]; SP = salesDetail.get(0)[6]; salesDate = salesDetail.get(0)[7]; SunitPrice = salesDetail.get(0)[8]; SBilll = salesDetail.get(0)[9]; SbillAmount = salesDetail.get(0)[10]; disReason = salesDetail.get(0)[11]; paymentMethd = salesDetail.get(0)[12]; VAT = salesDetail.get(0)[13]; SAccNo = salesDetail.get(0)[14]; SaccOpen = salesDetail.get(0)[15]; } // individualCreditSalesDetailList = accNatureSession.retrieveCheck(sales_id, getLoggedInOffice().getId()); return "stockLedgerWithValues.xhtml?faces-redirect=true"; } public String retrieveAllStockBetweenDate() throws ParseException { resetSum(); showRefresh = true; stockLedgerInquiryList = new ArrayList<PurchasSalesDTO>(); String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateReading = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate1"); startDate = nepaliDateConverter.convertToEnglishDate(startDateString); endDate = nepaliDateConverter.convertToEnglishDate(endDateReading); for (int g = 0; g < selectedItems.length; g++) { stockSaleBetweenDate = new ArrayList<Object>(); stockSaleBetweenDate = itemMasterSession.retrieveSalesStockBetweenDats(selectedItems[g], startDate, endDate, getLoggedInOffice().getId()); Double a = itemMasterSession.retrievePurchaseOpeningStock(selectedItems[g], startDate, getLoggedInOffice().getId()); Double b = itemMasterSession.retrieveOpeningSalesStock(selectedItems[g], startDate, getLoggedInOffice().getId()); Double c = itemMasterSession.retrievePurchaseOpeningItemPrice(selectedItems[g], startDate, getLoggedInOffice().getId()); stockPurchaseBetweenDate = new ArrayList<Object>(); stockPurchaseBetweenDate = itemMasterSession.retrievePurchaseStockBetweenDate(selectedItems[g], startDate, endDate, getLoggedInOffice().getId()); Double openingStock = a - b; IItemMaster name = itemMasterSession.retrieveItemsName(selectedItems[g]); PurchasSalesDTO q = new PurchasSalesDTO(); q.setCustomerSupplierName(" ITEM: " + name.getItemName().toUpperCase()); q.setQuantity(0.0); q.setValues(0.0); q.setTransactionType(" "); stockLedgerInquiryList.add(q); PurchasSalesDTO p = new PurchasSalesDTO(); p.setCustomerSupplierName("---------Opening Stock---------- "); p.setBalance(openingStock); p.setQuantity(openingStock); p.setUnitPrice(c); p.setValues(openingStock * c); p.setTransactionType("CO"); stockLedgerInquiryList.add(p); List<PurchasSalesDTO> iniList = new ArrayList<PurchasSalesDTO>(); for (int i = 0; i < stockSaleBetweenDate.size(); i++) { PurchasSalesDTO psdto = new PurchasSalesDTO(); Object[] row = (Object[]) stockSaleBetweenDate.get(i); psdto.setCustomerSupplierName(row[5].toString()); psdto.setDate(row[4].toString()); psdto.setHeaderId(Long.valueOf(row[2].toString())); psdto.setTransactionType("S"); psdto.setTransaction("Sales:" + row[9].toString()); SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String d = s.format((Date) row[7]); Date date = s.parse(d); psdto.setCompareDate(date); psdto.setItemName(row[5].toString()); psdto.setUnitPrice(Double.valueOf(row[8].toString())); psdto.setQuantity(Double.valueOf(row[2].toString())); psdto.setId(Long.valueOf(row[1].toString())); psdto.setItemId(Long.valueOf(row[10].toString())); psdto.setBill_no(row[9].toString()); iniList.add(psdto); sumSoldQtyy += psdto.getQuantity(); sumSoldAmount += (psdto.getUnitPrice() * psdto.getQuantity()); } //here for (int i = 0; i < stockPurchaseBetweenDate.size(); i++) { PurchasSalesDTO psdto = new PurchasSalesDTO(); Object[] row = (Object[]) stockPurchaseBetweenDate.get(i); psdto.setCustomerSupplierName(row[4].toString()); psdto.setTransactionType("P"); psdto.setTransaction("Purchase:" + row[6].toString()); psdto.setQuantity(Double.valueOf(row[0].toString())); psdto.setUnitPrice(Double.valueOf(row[1].toString())); psdto.setDate(row[3].toString()); SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String d = s.format((Date) row[5]); Date date = s.parse(d); psdto.setCompareDate(date); psdto.setId(Long.valueOf(row[7].toString())); psdto.setItemId(Long.valueOf(row[8].toString())); psdto.setBill_no(row[6].toString()); iniList.add(psdto); sumPurchaseQty += psdto.getQuantity(); sumPurchaseAmount += (psdto.getUnitPrice() * psdto.getQuantity()); } try { Collections.sort(iniList, new Comparator<PurchasSalesDTO>() { @Override public int compare(PurchasSalesDTO o1, PurchasSalesDTO o2) { return o1.getCompareDate().compareTo(o2.getCompareDate()); } }); } catch (Exception e) { e.printStackTrace(); } Double blnc = 0.0; Double vals = 0.0; double unitprice = 0.0; for (int i = 0; i < iniList.size(); i++) { try { if (iniList.get(i).getTransactionType().equals("P")) { iniList.get(i).setBalance(iniList.get(i - 1).getBalance() + iniList.get(i).getQuantity()); blnc = iniList.get(i - 1).getBalance() + iniList.get(i).getQuantity(); iniList.get(i).setValues((iniList.get(i - 1).getBalance() + iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice()); vals = (iniList.get(i - 1).getBalance() + iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice(); } else if (iniList.get(i).getTransactionType().equals("S")) { iniList.get(i).setBalance(iniList.get(i - 1).getBalance() - iniList.get(i).getQuantity()); blnc = iniList.get(i - 1).getBalance() - iniList.get(i).getQuantity(); iniList.get(i).setValues((iniList.get(i - 1).getBalance() - iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice()); vals = (iniList.get(i - 1).getBalance() - iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice(); } unitprice = iniList.get(i).getUnitPrice(); sumAvaviableQty += blnc; sumAvaviableAmount += (blnc * unitprice); } catch (Exception e) { if (iniList.get(i).getTransactionType().equals("P")) { iniList.get(i).setBalance(p.getQuantity() + iniList.get(i).getQuantity()); blnc = p.getQuantity() + iniList.get(i).getQuantity(); iniList.get(i).setValues( (p.getQuantity() + iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice()); vals = (p.getQuantity() + iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice(); } else if (iniList.get(i).getTransactionType().equals("S")) { iniList.get(i).setBalance(p.getQuantity() - iniList.get(i).getQuantity()); blnc = p.getQuantity() - iniList.get(i).getQuantity(); iniList.get(i).setValues( (p.getQuantity() - iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice()); vals = (p.getQuantity() - iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice(); } unitprice = iniList.get(i).getUnitPrice(); sumAvaviableQty += blnc; sumAvaviableAmount += (blnc * unitprice); } } stockLedgerInquiryList.addAll(iniList); PurchasSalesDTO ps = new PurchasSalesDTO(); ps.setCustomerSupplierName("---------Closing Stock----------- "); ps.setBalance(blnc); ps.setValues(vals); ps.setUnitPrice(unitprice); ps.setTransactionType("CO"); stockLedgerInquiryList.add(ps); } return "stockLedgerWithValues.xhtml"; } public String retrievePurchaseIssueStockBetweenDate() throws ParseException { resetSum(); showPrchaseIssueDatatable = true; showRefresh = true; stockLedgerInquiryList = new ArrayList<PurchasSalesDTO>(); String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateReading = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate1"); startDate = nepaliDateConverter.convertToEnglishDate(startDateString); endDate = nepaliDateConverter.convertToEnglishDate(endDateReading); for (int g = 0; g < selectedItems.length; g++) { itemIssueBetweenDate = new ArrayList<Object>(); itemIssueBetweenDate = itemMasterSession.retrieveItemsIssuedBetweenDats(selectedItems[g], startDate, endDate, getLoggedInOffice().getId()); Double a = itemMasterSession.retrievePurchaseOpeningStock(selectedItems[g], startDate, getLoggedInOffice().getId()); Double b = itemMasterSession.retrieveItemsIssuedStock(selectedItems[g], startDate, getLoggedInOffice().getId()); Double c = itemMasterSession.retrievePurchaseOpeningItemPrice(selectedItems[g], startDate, getLoggedInOffice().getId()); stockPurchaseBetweenDate = new ArrayList<Object>(); stockPurchaseBetweenDate = itemMasterSession.retrievePurchaseStockBetweenDate(selectedItems[g], startDate, endDate, getLoggedInOffice().getId()); Double openingStock = a - b; purchaseReturnBetweenDates = new ArrayList<Object>(); purchaseReturnBetweenDates = itemMasterSession.retrieveAllPurchaseReturnBetweenDate(selectedItems[g], startDate, endDate, getLoggedInOffice().getId()); IItemMaster name = itemMasterSession.retrieveItemsName(selectedItems[g]); PurchasSalesDTO q = new PurchasSalesDTO(); q.setCustomerSupplierName(" ITEM: " + name.getItemName().toUpperCase()); q.setQuantity(0.0); q.setValues(0.0); q.setTransactionType(" "); stockLedgerInquiryList.add(q); PurchasSalesDTO p = new PurchasSalesDTO(); p.setCustomerSupplierName("---------Opening Stock---------- "); p.setBalance(openingStock); p.setQuantity(openingStock); p.setUnitPrice(c); p.setValues(openingStock * c); p.setTransactionType("CO"); stockLedgerInquiryList.add(p); List<PurchasSalesDTO> iniList = new ArrayList<PurchasSalesDTO>(); for (int i = 0; i < itemIssueBetweenDate.size(); i++) { PurchasSalesDTO psdto = new PurchasSalesDTO(); Object[] row = (Object[]) itemIssueBetweenDate.get(i); psdto.setCustomerSupplierName(" "); psdto.setDate(row[4].toString()); psdto.setHeaderId(Long.valueOf(row[2].toString())); psdto.setTransactionType("S"); psdto.setTransaction("ISSUED:" + row[9].toString()); SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String d = s.format((Date) row[7]); Date date = s.parse(d); psdto.setCompareDate(date); psdto.setItemName(row[5].toString()); psdto.setUnitPrice(Double.valueOf(row[8].toString())); psdto.setQuantity(Double.valueOf(row[3].toString())); psdto.setId(Long.valueOf(row[1].toString())); psdto.setItemId(Long.valueOf(row[10].toString())); psdto.setBill_no(row[9].toString()); iniList.add(psdto); sumSoldQtyy += psdto.getQuantity(); sumSoldAmount += (psdto.getUnitPrice() * psdto.getQuantity()); } for (int i = 0; i < purchaseReturnBetweenDates.size(); i++) { PurchasSalesDTO psdto = new PurchasSalesDTO(); Object[] row = (Object[]) purchaseReturnBetweenDates.get(i); psdto.setCustomerSupplierName(row[5].toString()); psdto.setDate(row[4].toString()); psdto.setHeaderId(Long.valueOf(row[2].toString())); psdto.setTransactionType("PR"); psdto.setTransaction("Purchase Return:" + row[8].toString()); SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String d = s.format((Date) row[6]); Date date = s.parse(d); psdto.setCompareDate(date); psdto.setItemName(row[0].toString()); psdto.setUnitPrice(Double.valueOf(row[7].toString())); psdto.setQuantity(Double.valueOf(row[3].toString())); psdto.setId(Long.valueOf(row[1].toString())); psdto.setItemId(Long.valueOf(row[9].toString())); // psdto.setBill_no(row[9].toString()); + row[8].toString() iniList.add(psdto); sumPurchaseRetQty += psdto.getQuantity(); sumPurchaseRetAmount += (psdto.getQuantity() * psdto.getUnitPrice()); } for (int i = 0; i < stockPurchaseBetweenDate.size(); i++) { PurchasSalesDTO psdto = new PurchasSalesDTO(); Object[] row = (Object[]) stockPurchaseBetweenDate.get(i); psdto.setCustomerSupplierName(row[4].toString()); psdto.setTransactionType("P"); psdto.setTransaction("Purchase:" + row[6].toString()); psdto.setQuantity(Double.valueOf(row[0].toString())); psdto.setUnitPrice(Double.valueOf(row[1].toString())); psdto.setDate(row[3].toString()); SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String d = s.format((Date) row[5]); Date date = s.parse(d); psdto.setCompareDate(date); psdto.setId(Long.valueOf(row[7].toString())); psdto.setItemId(Long.valueOf(row[8].toString())); psdto.setBill_no(row[6].toString()); iniList.add(psdto); sumPurchaseQty += psdto.getQuantity(); sumPurchaseAmount += (psdto.getUnitPrice() * psdto.getQuantity()); } try { Collections.sort(iniList, new Comparator<PurchasSalesDTO>() { @Override public int compare(PurchasSalesDTO o1, PurchasSalesDTO o2) { return o1.getCompareDate().compareTo(o2.getCompareDate()); } }); } catch (Exception e) { e.printStackTrace(); } Double blnc = 0.0; Double vals = 0.0; double unitprice = 0.0; for (int i = 0; i < iniList.size(); i++) { try { if (iniList.get(i).getTransactionType().equals("P")) { iniList.get(i).setBalance(iniList.get(i - 1).getBalance() + iniList.get(i).getQuantity()); blnc = iniList.get(i - 1).getBalance() + iniList.get(i).getQuantity(); iniList.get(i).setValues((iniList.get(i - 1).getBalance() + iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice()); vals = (iniList.get(i - 1).getBalance() + iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice(); } else if (iniList.get(i).getTransactionType().equals("S") || iniList.get(i).getTransactionType().equals("PR")) { iniList.get(i).setBalance(iniList.get(i - 1).getBalance() - iniList.get(i).getQuantity()); blnc = iniList.get(i - 1).getBalance() - iniList.get(i).getQuantity(); iniList.get(i).setValues((iniList.get(i - 1).getBalance() - iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice()); vals = (iniList.get(i - 1).getBalance() - iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice(); } unitprice = iniList.get(i).getUnitPrice(); sumAvaviableQty += blnc; sumAvaviableAmount += (blnc * unitprice); } catch (Exception e) { if (iniList.get(i).getTransactionType().equals("P")) { iniList.get(i).setBalance(p.getQuantity() + iniList.get(i).getQuantity()); blnc = p.getQuantity() + iniList.get(i).getQuantity(); iniList.get(i).setValues( (p.getQuantity() + iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice()); vals = (p.getQuantity() + iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice(); } else if (iniList.get(i).getTransactionType().equals("S") || iniList.get(i).getTransactionType().equals("PR")) { iniList.get(i).setBalance(p.getQuantity() - iniList.get(i).getQuantity()); blnc = p.getQuantity() - iniList.get(i).getQuantity(); iniList.get(i).setValues( (p.getQuantity() - iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice()); vals = (p.getQuantity() - iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice(); } unitprice = iniList.get(i).getUnitPrice(); sumAvaviableQty += blnc; sumAvaviableAmount += (blnc * unitprice); } } stockLedgerInquiryList.addAll(iniList); PurchasSalesDTO ps = new PurchasSalesDTO(); ps.setCustomerSupplierName("---------Closing Stock----------- "); if (iniList.size() > 0) { ps.setBalance(blnc); ps.setValues(vals); } else { ps.setBalance(openingStock); } ps.setUnitPrice(unitprice); ps.setTransactionType("CO"); stockLedgerInquiryList.add(ps); } return "reportPurchaseIssue.xhtml"; } public String retrieveFinishedGoodStockBetweenDate() throws ParseException { resetSum(); showPrchaseIssueDatatable = true; showRefresh = true; stockLedgerInquiryList = new ArrayList<PurchasSalesDTO>(); String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateReading = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate1"); startDate = nepaliDateConverter.convertToEnglishDate(startDateString); endDate = nepaliDateConverter.convertToEnglishDate(endDateReading); for (int g = 0; g < selectedItems.length; g++) { stockSaleBetweenDate = new ArrayList<Object>(); stockSaleBetweenDate = itemMasterSession.retrieveSalesStockBetweenDats(selectedItems[g], startDate, endDate, getLoggedInOffice().getId()); Double a = itemMasterSession.retrievePurchaseOpeningStock(selectedItems[g], startDate, getLoggedInOffice().getId()); Double b = itemMasterSession.retrieveOpeningSalesStock(selectedItems[g], startDate, getLoggedInOffice().getId()); Double c = itemMasterSession.retrievePurchaseOpeningItemPrice(selectedItems[g], startDate, getLoggedInOffice().getId()); stockPurchaseBetweenDate = new ArrayList<Object>(); stockPurchaseBetweenDate = itemMasterSession.retrievePurchaseStockBetweenDate(selectedItems[g], startDate, endDate, getLoggedInOffice().getId()); Double openingStock = a - b; salesReturnBetweenDates = new ArrayList<Object>(); salesReturnBetweenDates = itemMasterSession.retrieveAllSalesReturnBetweenDate(selectedItems[g], startDate, endDate, getLoggedInOffice().getId()); IItemMaster name = itemMasterSession.retrieveItemsName(selectedItems[g]); PurchasSalesDTO q = new PurchasSalesDTO(); q.setCustomerSupplierName(" ITEM: " + name.getItemName().toUpperCase()); q.setQuantity(0.0); q.setValues(0.0); q.setTransactionType(" "); stockLedgerInquiryList.add(q); PurchasSalesDTO p = new PurchasSalesDTO(); p.setCustomerSupplierName("---------Opening Stock---------- "); p.setBalance(openingStock); p.setQuantity(openingStock); p.setUnitPrice(c); p.setValues(openingStock * c); p.setTransactionType("CO"); stockLedgerInquiryList.add(p); List<PurchasSalesDTO> iniList = new ArrayList<PurchasSalesDTO>(); for (int i = 0; i < stockPurchaseBetweenDate.size(); i++) { PurchasSalesDTO psdto = new PurchasSalesDTO(); Object[] row = (Object[]) stockPurchaseBetweenDate.get(i); psdto.setTransactionType("P"); psdto.setTransaction("Purchase:" + row[6].toString()); psdto.setQuantity(Double.valueOf(row[0].toString())); psdto.setUnitPrice(Double.valueOf(row[1].toString())); psdto.setDate(row[3].toString()); SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String d = s.format((Date) row[5]); Date date = s.parse(d); psdto.setCompareDate(date); psdto.setId(Long.valueOf(row[7].toString())); psdto.setItemName(row[5].toString()); psdto.setBill_no(row[4].toString()); iniList.add(psdto); sumPurchaseQty += psdto.getQuantity(); sumPurchaseAmount += (psdto.getUnitPrice() * psdto.getQuantity()); } for (int i = 0; i < stockSaleBetweenDate.size(); i++) { PurchasSalesDTO psdto = new PurchasSalesDTO(); Object[] row = (Object[]) stockSaleBetweenDate.get(i); psdto.setCustomerSupplierName(row[5].toString()); psdto.setDate(row[4].toString()); psdto.setHeaderId(Long.valueOf(row[2].toString())); psdto.setTransactionType("S"); psdto.setTransaction("Sales:" + row[9].toString()); SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String d = s.format((Date) row[7]); Date date = s.parse(d); psdto.setCompareDate(date); psdto.setItemName(row[0].toString()); psdto.setUnitPrice(Double.valueOf(row[8].toString())); psdto.setQuantity(Double.valueOf(row[3].toString())); psdto.setId(Long.valueOf(row[1].toString())); psdto.setItemId(Long.valueOf(row[10].toString())); psdto.setBill_no(row[9].toString()); iniList.add(psdto); sumSoldQtyy += psdto.getQuantity(); sumSoldAmount += (psdto.getUnitPrice() * psdto.getQuantity()); } for (int i = 0; i < salesReturnBetweenDates.size(); i++) { PurchasSalesDTO psdto = new PurchasSalesDTO(); Object[] row = (Object[]) salesReturnBetweenDates.get(i); psdto.setCustomerSupplierName(row[6].toString()); psdto.setDate(row[4].toString()); psdto.setHeaderId(Long.valueOf(row[2].toString())); psdto.setTransactionType("SR"); psdto.setTransaction("Sales Return:" + row[8].toString()); SimpleDateFormat s = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String d = s.format((Date) row[5]); Date date = s.parse(d); psdto.setCompareDate(date); psdto.setItemName(row[0].toString()); psdto.setUnitPrice(Double.valueOf(row[7].toString())); psdto.setQuantity(Double.valueOf(row[3].toString())); psdto.setId(Long.valueOf(row[1].toString())); psdto.setItemId(Long.valueOf(row[9].toString())); // psdto.setBill_no(row[9].toString()); + row[8].toString() iniList.add(psdto); sumSalesRetQty += psdto.getQuantity(); sumSalesRetAmount += (psdto.getUnitPrice() * psdto.getQuantity()); } try { Collections.sort(iniList, new Comparator<PurchasSalesDTO>() { @Override public int compare(PurchasSalesDTO o1, PurchasSalesDTO o2) { return o1.getCompareDate().compareTo(o2.getCompareDate()); } }); } catch (Exception e) { e.printStackTrace(); } Double blnc = 0.0; Double vals = 0.0; double unitprice = 0.0; for (int i = 0; i < iniList.size(); i++) { try { if (iniList.get(i).getTransactionType().equals("P")) { iniList.get(i).setBalance(iniList.get(i - 1).getBalance() + iniList.get(i).getQuantity()); blnc = iniList.get(i - 1).getBalance() + iniList.get(i).getQuantity(); iniList.get(i).setValues((iniList.get(i - 1).getBalance() + iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice()); vals = (iniList.get(i - 1).getBalance() + iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice(); } else if (iniList.get(i).getTransactionType().equals("S") || iniList.get(i).getTransactionType().equals("SR")) { iniList.get(i).setBalance(iniList.get(i - 1).getBalance() - iniList.get(i).getQuantity()); blnc = iniList.get(i - 1).getBalance() - iniList.get(i).getQuantity(); iniList.get(i).setValues((iniList.get(i - 1).getBalance() - iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice()); vals = (iniList.get(i - 1).getBalance() - iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice(); } unitprice = iniList.get(i).getUnitPrice(); sumAvaviableQty += blnc; sumAvaviableAmount += (blnc * unitprice); } catch (Exception e) { if (iniList.get(i).getTransactionType().equals("P")) { iniList.get(i).setBalance(p.getQuantity() + iniList.get(i).getQuantity()); blnc = p.getQuantity() + iniList.get(i).getQuantity(); iniList.get(i).setValues( (p.getQuantity() + iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice()); vals = (p.getQuantity() + iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice(); } else if (iniList.get(i).getTransactionType().equals("S") || iniList.get(i).getTransactionType().equals("SR")) { iniList.get(i).setBalance(p.getQuantity() - iniList.get(i).getQuantity()); blnc = p.getQuantity() - iniList.get(i).getQuantity(); iniList.get(i).setValues( (p.getQuantity() - iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice()); vals = (p.getQuantity() - iniList.get(i).getQuantity()) * iniList.get(i).getUnitPrice(); } unitprice = iniList.get(i).getUnitPrice(); sumAvaviableQty += blnc; sumAvaviableAmount += (blnc * unitprice); } } stockLedgerInquiryList.addAll(iniList); PurchasSalesDTO ps = new PurchasSalesDTO(); ps.setCustomerSupplierName("---------Closing Stock----------- "); ps.setBalance(blnc); ps.setValues(vals); ps.setUnitPrice(unitprice); ps.setTransactionType("CO"); stockLedgerInquiryList.add(ps); } return "reportFinishedGood.xhtml"; } public List<Object> getItemIssueBetweenDate() { return itemIssueBetweenDate; } public void setItemIssueBetweenDate(List<Object> itemIssueBetweenDate) { this.itemIssueBetweenDate = itemIssueBetweenDate; } public void showDetailsPopUp(String type) { if (type.equals("P")) { RequestContext.getCurrentInstance().execute("PF('purchase').show()"); } else { RequestContext.getCurrentInstance().execute("PF('sale').show()"); } } public List<IItemMaster> getItemsList() { return itemsList; } public List<IItemMaster> getItemId() { return itemId; } public void setItemId(List<IItemMaster> itemId) { this.itemId = itemId; } public void setItemsList(List<IItemMaster> itemsList) { this.itemsList = itemsList; } public Office getLoggedInOffice() { Office o = (Office) Util.getSession().getAttribute("office"); return o; } public Date getStartDate() { return startDate; } public void setStartDate(Date startDate) { this.startDate = startDate; } public Date getEndDate() { return endDate; } public void setEndDate(Date endDate) { this.endDate = endDate; } public Date getDateStart() { return dateStart; } public void setDateStart(Date dateStart) { this.dateStart = dateStart; } public Date getDateEnd() { return dateEnd; } public void setDateEnd(Date dateEnd) { this.dateEnd = dateEnd; } public List<PurchasSalesDTO> getStockLedgerInquiryList() { return stockLedgerInquiryList; } public void setStockLedgerInquiryList(List<PurchasSalesDTO> stockLedgerInquiryList) { this.stockLedgerInquiryList = stockLedgerInquiryList; } public Long[] getSelectedItems() { return selectedItems; } public void setSelectedItems(Long[] selectedItems) { this.selectedItems = selectedItems; } public boolean isShowRefresh() { return showRefresh; } public void setShowRefresh(boolean showRefresh) { this.showRefresh = showRefresh; } public ItemMasterSession getItemMasterSession() { return itemMasterSession; } public void setItemMasterSession(ItemMasterSession itemMasterSession) { this.itemMasterSession = itemMasterSession; } public List<Object> getStockSaleBetweenDate() { return stockSaleBetweenDate; } public void setStockSaleBetweenDate(List<Object> stockSaleBetweenDate) { this.stockSaleBetweenDate = stockSaleBetweenDate; } public List<Object> getStockPurchaseBetweenDate() { return stockPurchaseBetweenDate; } public void setStockPurchaseBetweenDate(List<Object> stockPurchaseBetweenDate) { this.stockPurchaseBetweenDate = stockPurchaseBetweenDate; } public List<Object[]> getPurchaseDetail() { return purchaseDetail; } public void setPurchaseDetail(List<Object[]> purchaseDetail) { this.purchaseDetail = purchaseDetail; } public List<ISalesDetail> getIndividualCreditSalesDetailList() { return individualCreditSalesDetailList; } public void setIndividualCreditSalesDetailList(List<ISalesDetail> individualCreditSalesDetailList) { this.individualCreditSalesDetailList = individualCreditSalesDetailList; } public Object getPurchaseQty() { return purchaseQty; } public void setPurchaseQty(Object purchaseQty) { this.purchaseQty = purchaseQty; } public Object getUnitPrice() { return unitPrice; } public void setUnitPrice(Object unitPrice) { this.unitPrice = unitPrice; } public Object getMrp() { return mrp; } public void setMrp(Object mrp) { this.mrp = mrp; } public Object getPurchaseDateNepali() { return purchaseDateNepali; } public void setPurchaseDateNepali(Object purchaseDateNepali) { this.purchaseDateNepali = purchaseDateNepali; } public Object getName() { return name; } public void setName(Object name) { this.name = name; } public Object getPurchaseDate() { return purchaseDate; } public void setPurchaseDate(Object purchaseDate) { this.purchaseDate = purchaseDate; } public Object getBill() { return bill; } public void setBill(Object bill) { this.bill = bill; } public Object getId() { return id; } public void setId(Object id) { this.id = id; } public Object getRefNo() { return refNo; } public void setRefNo(Object refNo) { this.refNo = refNo; } public Object getAccNumber() { return accNumber; } public void setAccNumber(Object accNumber) { this.accNumber = accNumber; } public Object getWareHouse() { return wareHouse; } public void setWareHouse(Object wareHouse) { this.wareHouse = wareHouse; } public Object getLocation() { return location; } public void setLocation(Object location) { this.location = location; } public List<Object[]> getSalesDetail() { return salesDetail; } public void setSalesDetail(List<Object[]> salesDetail) { this.salesDetail = salesDetail; } public Object getItemname() { return itemname; } public void setItemname(Object itemname) { this.itemname = itemname; } public Object getSid() { return Sid; } public void setSid(Object Sid) { this.Sid = Sid; } public Object getShdId() { return shdId; } public void setShdId(Object shdId) { this.shdId = shdId; } public Object getsQty() { return sQty; } public void setsQty(Object sQty) { this.sQty = sQty; } public Object getSDateNepali() { return SDateNepali; } public void setSDateNepali(Object SDateNepali) { this.SDateNepali = SDateNepali; } public Object getSAccName() { return SAccName; } public void setSAccName(Object SAccName) { this.SAccName = SAccName; } public Object getSP() { return SP; } public void setSP(Object SP) { this.SP = SP; } public Object getSalesDate() { return salesDate; } public void setSalesDate(Object salesDate) { this.salesDate = salesDate; } public Object getSunitPrice() { return SunitPrice; } public void setSunitPrice(Object SunitPrice) { this.SunitPrice = SunitPrice; } public Object getSBilll() { return SBilll; } public void setSBilll(Object SBilll) { this.SBilll = SBilll; } public Object getSbillAmount() { return SbillAmount; } public void setSbillAmount(Object SbillAmount) { this.SbillAmount = SbillAmount; } public Object getDisReason() { return disReason; } public void setDisReason(Object disReason) { this.disReason = disReason; } public Object getPaymentMethd() { return paymentMethd; } public void setPaymentMethd(Object paymentMethd) { this.paymentMethd = paymentMethd; } public Object getVAT() { return VAT; } public void setVAT(Object VAT) { this.VAT = VAT; } public Object getSAccNo() { return SAccNo; } public void setSAccNo(Object SAccNo) { this.SAccNo = SAccNo; } public Object getSaccOpen() { return SaccOpen; } public void setSaccOpen(Object SaccOpen) { this.SaccOpen = SaccOpen; } public void postProcessXLS(Object document) { String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); String endDateReading = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate1"); 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(0); firstRow.createCell(0).setCellValue(reportHeader); firstRow.getCell(0).setCellStyle(headerCellStyle); HSSFRow secondRow = sheet.createRow(1); 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); System.out.println("The cell count " + cellCount); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellCount)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, cellCount)); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, cellCount)); sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, cellCount)); for (Row row : sheet) { if (row.getRowNum() > 4) { for (int i = 3; i < count; i++) { String cost = row.getCell(i).getStringCellValue(); if (cost != null && !cost.isEmpty()) { row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK); row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC); row.getCell(i).setCellValue(Double.parseDouble(cost.replace(",", ""))); } } } } } public String getPreviousDate() { String previousDate = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate4"); if (previousDate == null || previousDate.isEmpty()) { previousDate = ndc.convertToNepaliDate(new Date()); } return previousDate; } public String getCurrentDate() { String currentDate = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap() .get("nepaliDate1"); if (currentDate == null || currentDate.isEmpty()) { currentDate = ndc.convertToNepaliDate(new Date()); } return currentDate; } public User getLoggedInUser() { User user = (User) Util.getSession().getAttribute("user"); return user; } public List<IItemMaster> getItemsListRaw() { return itemsListRaw; } public void setItemsListRaw(List<IItemMaster> itemsListRaw) { this.itemsListRaw = itemsListRaw; } public List<Object> getPurchaseReturnBetweenDates() { return purchaseReturnBetweenDates; } public void setPurchaseReturnBetweenDates(List<Object> purchaseReturnBetweenDates) { this.purchaseReturnBetweenDates = purchaseReturnBetweenDates; } public boolean isShowPrchaseIssueDatatable() { return showPrchaseIssueDatatable; } public void setShowPrchaseIssueDatatable(boolean showPrchaseIssueDatatable) { this.showPrchaseIssueDatatable = showPrchaseIssueDatatable; } public List<IItemMaster> getItemsListFinishedGood() { return itemsListFinishedGood; } public void setItemsListFinishedGood(List<IItemMaster> itemsListFinishedGood) { this.itemsListFinishedGood = itemsListFinishedGood; } public List<Object> getSalesReturnBetweenDates() { return salesReturnBetweenDates; } public void setSalesReturnBetweenDates(List<Object> salesReturnBetweenDates) { this.salesReturnBetweenDates = salesReturnBetweenDates; } public double getSumPurchaseQty() { return sumPurchaseQty; } public void setSumPurchaseQty(double sumPurchaseQty) { this.sumPurchaseQty = sumPurchaseQty; } public double getSumPurchaseAmount() { return sumPurchaseAmount; } public void setSumPurchaseAmount(double sumPurchaseAmount) { this.sumPurchaseAmount = sumPurchaseAmount; } public double getSumSoldQtyy() { return sumSoldQtyy; } public void setSumSoldQtyy(double sumSoldQtyy) { this.sumSoldQtyy = sumSoldQtyy; } public double getSumSoldQty() { return sumSoldQty; } public void setSumSoldQty(double sumSoldQty) { this.sumSoldQty = sumSoldQty; } public double getSumAvaviableQty() { return sumAvaviableQty; } public void setSumAvaviableQty(double sumAvaviableQty) { this.sumAvaviableQty = sumAvaviableQty; } public double getSumAvaviableAmount() { return sumAvaviableAmount; } public void setSumAvaviableAmount(double sumAvaviableAmount) { this.sumAvaviableAmount = sumAvaviableAmount; } public double getSumSoldAmount() { return sumSoldAmount; } public void setSumSoldAmount(double sumSoldAmount) { this.sumSoldAmount = sumSoldAmount; } public double getSumPurchaseRetQty() { return sumPurchaseRetQty; } public void setSumPurchaseRetQty(double sumPurchaseRetQty) { this.sumPurchaseRetQty = sumPurchaseRetQty; } public double getSumPurchaseRetAmount() { return sumPurchaseRetAmount; } public void setSumPurchaseRetAmount(double sumPurchaseRetAmount) { this.sumPurchaseRetAmount = sumPurchaseRetAmount; } public double getSumSalesRetQty() { return sumSalesRetQty; } public void setSumSalesRetQty(double sumSalesRetQty) { this.sumSalesRetQty = sumSalesRetQty; } public double getSumSalesRetAmount() { return sumSalesRetAmount; } public void setSumSalesRetAmount(double sumSalesRetAmount) { this.sumSalesRetAmount = sumSalesRetAmount; } }