Subversion Repositories SmartDukaan

package in.shop2020.support.controllers;

import in.shop2020.logistics.Provider;
import in.shop2020.model.v1.inventory.Vendor;
import in.shop2020.model.v1.inventory.InventoryService.Client;
import in.shop2020.model.v1.order.LineItem;
import in.shop2020.model.v1.order.Order;
import in.shop2020.model.v1.order.OrderStatus;
import in.shop2020.model.v1.order.OrderStatusGroups;
import in.shop2020.model.v1.order.PaymentSettlement;
import in.shop2020.model.v1.order.ReturnOrder;
import in.shop2020.model.v1.order.Transaction;
import in.shop2020.model.v1.order.TransactionServiceException;
import in.shop2020.payments.Payment;
import in.shop2020.payments.PaymentGateway;
import in.shop2020.thrift.clients.InventoryClient;
import in.shop2020.thrift.clients.LogisticsClient;
import in.shop2020.thrift.clients.PaymentClient;
import in.shop2020.thrift.clients.TransactionClient;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.Map.Entry;

import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.struts2.interceptor.ServletRequestAware;
import org.apache.struts2.interceptor.ServletResponseAware;
import org.apache.struts2.rest.DefaultHttpHeaders;
import org.apache.struts2.rest.HttpHeaders;
import org.apache.struts2.util.ServletContextAware;
import org.apache.thrift.transport.TTransportException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.opensymphony.xwork2.ValidationAwareSupport;

/**
 * @author Varun Gupta
 * @description: This class handles web requests to generate vendor specific reconciliation reports
 *               for given vendor for a given date range
 */

public class VendorReconciliationController extends ValidationAwareSupport implements ServletRequestAware, ServletResponseAware, ServletContextAware {

    private static final long serialVersionUID = 1L;
        private static Logger logger = LoggerFactory.getLogger(VendorReconciliationController.class);
    
        private enum OrderReportColumn {
            ORDER_ID(0),
            ORIGINAL_ORDER_ID(1),
            VENDOR_ID(2),
            VENDOR_NAME(3),
            ORDER_DATE(4),
                BILLING_NUMBER(5),
                BILLING_DATE(6),
                DELIVERY_DATE(7),
                BRAND(8),
                MODEL_NAME(9),
                MODEL_NUMBER(10),
                COLOR(11),
                QUANTITY(12),
                UNIT_TRANSFER_PRICE(13),
                UNIT_SELLING_PRICE(14),
                TOTAL_TRANSFER_PRICE(15),
                TOTAL_SELLING_PRICE(16),
                CURRENT_STATUS(17),
                VENDOR_PAID(18),
                PAYMENT_STATUS(19),
                PAYMENT_TYPE(20),
                PAYMENT_ID(21),
                COUPON_CODE(22),
                SETTLEMENT_DATE(23),
                SERVICE_TAX(24),
                OTHER_CHARGES(25),
                NET_COLLECTION(26),
                USER_PAYABLE_AMOUNT(27);
            
                private int value;
                
                OrderReportColumn(int value) {
                        this.value = value;
                }
                public int getValue(){
                        return this.value;
                }
        }
        
        private HttpServletRequest request;
        private HttpServletResponse response;
        private ServletContext context;
        private List<Vendor> vendors;
        private Map<Long, String> codProviders = new HashMap<Long, String>();
        private Map<Long, String> paymentGateways = new HashMap<Long, String>();
        
        private File orderIdsToBeMarked;
        private File hdfcSettlementReport;
        
        private File ebsSettlementReport;
        private File ebsSettlementSummary;
        
        private String bluedartSettlementDate;
        private File bluedartSettlementReport;
        
        private String aramexSettlementDate;
        private File aramexSettlementReport;
        
        private String delhiverySettlementDate;
        private File delhiverySettlementReport;
        
        private Map<Long, String> ebsSettlementSummaries;
        
        private List<OrderStatus> refundOrderStatuses;

        private List<Order> reshippedNonPayableOrders = new ArrayList<Order>();
        private List<Order> returnedOrders = new ArrayList<Order>();
        private List<Order> unsettledOrders = new ArrayList<Order>();
        private List<Order> userPaymentNotRequiredOrders = new ArrayList<Order>();
        private Map<Order, PaymentSettlement> paymentSettledOrdersMap = new HashMap<Order, PaymentSettlement>();
        private List<Order> ordersWithErrors = new ArrayList<Order>();
        private Map<Long, String> vendorIdNameMap;
        
        private final DateFormat DATE_FORMAT = new SimpleDateFormat("dd/MM/yyyy");
        
        public String index()   {
                initiatializeDashboard();
                return "report";
        }
        
        public HttpHeaders create()     {
                
                String formType = request.getParameter("formtype");
                logger.info("Form Type: " + formType);
                
                if (formType.equals("uploadEBSSettlementSummary")) {
                        uploadEBSSettlementSummary();
                        
                } else if (formType.equals("uploadEBSSettlements")) {
                        uploadEBSSettlements();
                        
                } else if (formType.equals("uploadHDFCSettlements")) {
                        uploadHDFCSettlements();
                        
                } else if (formType.equals("uploadBluedartSettlements")) {
                        uploadBluedartSettlements();
                        
                } else if (formType.equals("uploadAramexSettlements")) {
                        uploadAramexSettlements();
                        
                } else if (formType.equals("markOrdersAsPaid")) {
                        uploadOrderIdsToBeMarkedPaid();
                        
                } else if (formType.equals("markOrdersAsReturned"))     {
                        uploadOrderIdsToBeMarkedReturned();
                        
                } else if (formType.equals("uploadDelhiverySettlements"))       {
                        uploadDelhiverySettlements();
                        
                } 
                
                initiatializeDashboard();
                return new DefaultHttpHeaders("report");
        }
        
        public HttpHeaders generateReconciliationReport()       {
                initiatializeDashboard();
                
                try {
                        LogisticsClient lsc = new LogisticsClient();
                in.shop2020.logistics.LogisticsService.Client logisticsClient = lsc.getClient();
                
                PaymentClient psc = new PaymentClient();
                in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
                
                        List<PaymentGateway> tPaymentGateways = paymentClient.getActivePaymentGateways();
                
                for (PaymentGateway tPaymentGateway: tPaymentGateways)  {
                        paymentGateways.put(tPaymentGateway.getId(), tPaymentGateway.getName());
                }
                
                for (Provider provider: logisticsClient.getAllProviders())      {
                        codProviders.put(provider.getId(), provider.getName());
                }
        
                        try     {
                                DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
                                String formType = request.getParameter("formtype");
                                long fromTime = 0;
                                long toTime = 0;
                                boolean notall = true;
                                
                                if (formType.equals("downloadbydate")) {
                                        if(! request.getParameter("start").isEmpty() && ! request.getParameter("end").isEmpty()) {
                                                Date startDate = dateFormat.parse(request.getParameter("start"));
                                            fromTime = startDate.getTime();
                                            
                                            Date endDate = dateFormat.parse(request.getParameter("end"));
                                            toTime = endDate.getTime();
                                        }
                                        else {
                                                String errMsg = "No Date Range given";
                                                logger.error(errMsg);
                                                addActionError(errMsg);
                                                return new DefaultHttpHeaders("report");
                                        }
                                        notall = false;
                                        
                                }
                                
                            long vendorId = Long.parseLong(request.getParameter("vendor"));
                            String vendorName = "";
                            logger.info("Vendor ID: " + vendorId);
                                
                            if(vendorId == -1) {
                                vendorName = "ALL-Vendors";
                            }
                            else {
                                vendorName = getVendorName(vendorId);
                                if(vendorName != null) {
                                        vendorName = vendorName.replaceAll("\\s", "-");
                                }
                            }
                            
                                response.setContentType("application/vnd.ms-excel");
                                response.setHeader("Content-disposition", "inline; filename=" + vendorName + "-reconciliation.xls");
                                
                                ServletOutputStream sos;
                                try {
                                        ByteArrayOutputStream baos = getReconciliationReport(vendorId, fromTime, toTime, notall);
                                        sos = response.getOutputStream();
                                        baos.writeTo(sos);
                                        sos.flush();
                                } catch (IOException e) {
                                        String errMsg = "Error while streaming the reconciliation report";
                                        logger.error(errMsg, e);
                                        addActionError(errMsg);
                                }
                        } catch (NullPointerException e) {
                    String errMsg = "NullPointerException";
                                logger.error(errMsg, e);
                                addActionError(errMsg);
                        } catch (ParseException e) {
                        String errMsg = "Could not parse the Dates";
                        logger.error(errMsg, e);
                                addActionError(errMsg);
                }
                        
                } catch (Exception e) {
                        String errMsg = "Exception Occured in generateReconciliationReport";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
                
                return new DefaultHttpHeaders("report");
        }

        public void uploadEBSSettlementSummary()        {

                DateFormat settlementDateFormat = new SimpleDateFormat("dd MMM,yyyy hh:mm:ss a");
                DateFormat transactionDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                
                Workbook wb = null;
        try {
            wb = new HSSFWorkbook(new FileInputStream(ebsSettlementSummary));
        } catch (FileNotFoundException e) {
                String errMsg = "Unable to open the EBS Settlement Summary report";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
        } catch (IOException e) {
                String errMsg = "Unable to open the EBS Settlement Summary report";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
        }
        Sheet sheet = wb.getSheetAt(0);
        
        int indexSettlementId = 0;
        int indexSettlementDate = 1;
        int indexTransactionDateFrom = 2;
        int indexTransactionDateTo = 3;
        int indexAmount = 4;
        
                TransactionClient tsc;
                int countSuccessfulSaves = 0;
                long settlementId = 0;
                
                try {
                        tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        
                        for(Row row: sheet)     {
                                if(row.getRowNum() < 5) {
                                continue;
                        }
                                
                                try     {
                                        settlementId = (long) row.getCell(indexSettlementId).getNumericCellValue();
                                        String settlementDateStr = row.getCell(indexSettlementDate).getStringCellValue();
                                        String transactionDateFromStr = row.getCell(indexTransactionDateFrom).getStringCellValue();
                                        String transactionDateToStr = row.getCell(indexTransactionDateTo).getStringCellValue();
                                        Double amount = row.getCell(indexAmount).getNumericCellValue();
                                        
                                        Date settlementDate = settlementDateFormat.parse(settlementDateStr);
                                        Date transactionDateFrom = transactionDateFormat.parse(transactionDateFromStr);
                                        Date transactionDateTo = transactionDateFormat.parse(transactionDateToStr);
                                        
                                        transactionClient.saveEBSSettlementSummary(settlementId, settlementDate.getTime(), transactionDateFrom.getTime(), transactionDateTo.getTime(), amount);
                                        countSuccessfulSaves += 1;
                                        
                                } catch (TransactionServiceException e) {
                        String errMsg = "TransactionServiceException recieved for settlement Id, " + Long.toString(settlementId);
                                logger.error(errMsg, e);
                                addActionError(errMsg);
                        } catch (Exception e) {
                        String errMsg = "Exception recieved for settlement Id, " + Long.toString(settlementId);
                                logger.error(errMsg, e);
                                addActionError(errMsg);
                        }
                        }
        } catch (Exception e) {
                        String errMsg = "Exception recieved for settlement Id," + Long.toString(settlementId);
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
        addActionMessage("Successfully added " + countSuccessfulSaves + " EBS Settlements");
        }
        
        public void uploadHDFCSettlements()     {
                logger.info("Uploading HDFC settlements");
                
                Workbook wb = null;
                try {
                        wb = new HSSFWorkbook(new FileInputStream(this.hdfcSettlementReport));
                } catch (FileNotFoundException e) {
                        String errMsg = "Unable to open the HDFC Payout report";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
                } catch (IOException e) {
                        String errMsg = "Unable to open the HDFC Payout report";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
                }
                Sheet sheet = wb.getSheetAt(0);
                
                int indexRECFMT = 2;
                int indexSettlementDate = 7;
                int indexPaymentId = 13;
                int indexMSF = 14;
                int indexServiceTax = 15;
                int indexEduCess = 16;
                int indexNetCollection = 17;
                
                TransactionClient tsc;
                String paymentIdStr = "";
                long paymentId = 0;
                
                int countSuccessfulInserts = 0;
                int countRefunds = 0;
                
                try {
                        tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        
                        for (Row row: sheet)    {
                                if(row.getRowNum() < 1) {
                                continue;
                        }
                
                try     {
                        
                        if(row.getCell(0).getStringCellValue().equals("Total")) {
                        break;
                    }
                        
                    String recfmt = row.getCell(indexRECFMT).getStringCellValue();
                    Date settlementDate = row.getCell(indexSettlementDate).getDateCellValue();
                        double netCollection = row.getCell(indexNetCollection).getNumericCellValue();
                        paymentIdStr = row.getCell(indexPaymentId).getStringCellValue().replace("'", "").trim();
                        paymentId = Long.parseLong(paymentIdStr);
                        
                        logger.info("paymentId: " + paymentId + ", recfmt: " + recfmt + ", settlementDate: " + settlementDate + ", netCollection: " + netCollection);
                        
                    if (recfmt.trim().equalsIgnoreCase("CVD"))  {
                        if (netCollection > 0.0)        netCollection *= -1.0;
                        transactionClient.savePaymentSettlements(settlementDate.getTime(), 1, paymentId, 0.0, 0.0, netCollection);
                        countRefunds += 1;
                    
                    } else      {
                        double msf = row.getCell(indexMSF).getNumericCellValue();
                        double serviceTax = row.getCell(indexServiceTax).getNumericCellValue();
                        double eduCess = row.getCell(indexEduCess).getNumericCellValue();
                        logger.info("msf: " + msf + ", serviceTax: " + serviceTax + ", eduCess: " + eduCess);
                        
                        transactionClient.savePaymentSettlements(settlementDate.getTime(), 1, paymentId, serviceTax, (eduCess + msf), netCollection);
                        countSuccessfulInserts += 1;
                        }
                } catch (NumberFormatException e) {
                        String errMsg = "NumberFormatException recieved for payment Id, " + paymentIdStr;
                                logger.error(errMsg, e);
                                addActionError(errMsg);
                                
                        } catch (TransactionServiceException e) {
                        String errMsg = "TransactionServiceException recieved for payment Id, " + Long.toString(paymentId);
                                logger.error(errMsg, e);
                                addActionError(errMsg);
                        } catch (Exception e) {
                                String errMsg = "Exception recieved for payment Id," + Long.toString(paymentId);
                                logger.error(errMsg, e);
                                addActionError(errMsg);
                        }
                        }
                } catch (Exception e) {
                        String errMsg = "Exception recieved for payment Id," + Long.toString(paymentId);
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
                
                addActionMessage("Added settlements for " + countSuccessfulInserts + " payments");
                addActionMessage("Added settlements for " + countRefunds + " refund payments");
        }
        
        public void uploadEBSSettlements()      {
                Workbook wb = null;
        try {
            wb = new HSSFWorkbook(new FileInputStream(this.ebsSettlementReport));
        } catch (FileNotFoundException e) {
                String errMsg = "Unable to open the EBS Settlement detail report";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
        } catch (IOException e) {
                String errMsg = "Unable to open the EBS Settlement detail report";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
        }
        
        Sheet sheet = wb.getSheetAt(0);
        
                Row firstRow = sheet.getRow(0);
                Row secondRow = sheet.getRow(1);
        
        int indexPaymentId = 2;
        int indexTxnType = 5;
        int indexAmount = 6;
        
        Map<Long, Map<String, Double>> map = new HashMap<Long, Map<String,Double>>();
        Map <String, Double> record;
        int countPaymentIds = 0;
        
        try     {
                for (Row row: sheet)    {
                if(row.equals(firstRow) || row.equals(secondRow))       continue;
                
                long paymentId = Long.parseLong(row.getCell(indexPaymentId).getStringCellValue());
                String transactionType = row.getCell(indexTxnType).getStringCellValue();
                double amount = row.getCell(indexAmount).getNumericCellValue();
                
                if(map.containsKey(paymentId))  {
                        map.get(paymentId).put(transactionType, amount);
                        
                } else  {
                        record = new HashMap<String, Double>();
                        record.put(transactionType, amount);
                        map.put(paymentId, record);
                        countPaymentIds += 1;
                }
                }
        } catch (NullPointerException e)        {
                logger.error("" + e);
        }
        addActionMessage("Total Payment Ids added: " + countPaymentIds);
        
                TransactionClient tsc;
        long settlementId = Long.parseLong(request.getParameter("settlementId"));
        
        int countPaymentsRecieved = 0;
        int countPaymentsRefunded = 0;
        
        try     {
                        tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        long settlementDate;
                        
                        try     {
                                settlementDate = transactionClient.getEBSSettlementDate(settlementId);
                        } catch (TransactionServiceException e) {
                                String errMsg = "Could not retrieve EBS Settlement date for settlementId, " + settlementId;
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
                        }
                        
                for (long paymentId: map.keySet())      {
                record = map.get(paymentId);
                
                if(record.containsKey("Captured"))      {
                        double capturedAmount = record.get("Captured");
                        double tdr = record.get("TDR");
                        double serviceTax = record.get("ServiceTax");
                        
                        if(tdr < 0)     tdr *= -1;
                        if(serviceTax < 0)      serviceTax *= -1;
                        
                        double amount = capturedAmount - tdr - serviceTax;
                        
                        try     {
                                transactionClient.savePaymentSettlements(settlementDate, 2, paymentId, serviceTax, tdr, amount);
                                } catch (TransactionServiceException e) {
                                        String errMsg = "Could not save settlement for paymentId, " + paymentId;
                                logger.error(errMsg + e);
                                addActionError(errMsg);
                                }
                        countPaymentsRecieved += 1;
                }
                
                if(record.containsKey("Refunded"))      {
                        double refundedAmount = record.get("Refunded");
                        
                        if (refundedAmount > 0) refundedAmount *= -1;
                        
                        try     {
                                transactionClient.savePaymentSettlements(settlementDate, 2, paymentId, 0.0, 0.0, refundedAmount);
                                } catch (TransactionServiceException e) {
                                        String errMsg = "Could not save refund settlement for paymentId, " + paymentId;
                                logger.error(errMsg, e);
                                addActionError(errMsg);
                                }
                        countPaymentsRefunded += 1;
                }
            }
                addActionMessage("Total count of payments recieved: " + countPaymentsRecieved);
                addActionMessage("Total count of payments refunded: " + countPaymentsRefunded);
                
                try     {
                        transactionClient.markEBSSettlementUploaded(settlementId);
                        } catch (TransactionServiceException e) {
                                String errMsg = "Could not mark settlement for details Uploaded. Settlement ID: " + settlementId;
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        }
        } catch (Exception e) {
                        String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
        }
        
        public void uploadBluedartSettlements() {
                
                Workbook wb = null;
        try {
            wb = new HSSFWorkbook(new FileInputStream(this.bluedartSettlementReport));
        } catch (FileNotFoundException e) {
                String errMsg = "Unable to open the Bluedart Settlement report";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
        } catch (IOException e) {
                String errMsg = "Unable to open the Bluedart Settlement report";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
        }
        
        Sheet sheet = wb.getSheetAt(0);
        
        int indexOrderId = 15;
        int indexAmount = 14;
        
        TransactionClient tsc;
        int countSuccessfulSaves = 0;
        
        try     {
                        long settlementDate = DATE_FORMAT.parse(bluedartSettlementDate).getTime();
                tsc = new TransactionClient();
                in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                
                long orderId = 0;
                
                for (Row row : sheet) {
                        String orderIdString = "";
                        
                        if(row.getRowNum() < 1) {
                                continue;
                        }
                        
                        try     {
                                orderIdString = row.getCell(indexOrderId).getStringCellValue(); 
                                orderId = Long.parseLong(orderIdString);
                                
                                double amount = row.getCell(indexAmount).getNumericCellValue();
                                logger.info("Bluedart SettlementDate: " + settlementDate + ", OrderId: " + orderId + ", Amount: " + amount);
                                transactionClient.savePaymentSettlements(settlementDate, 4, orderId, 0.0, 0.0, amount);
                                countSuccessfulSaves += 1;
                                
                        } catch (IllegalStateException e) {
                                String errMsg = "IllegalStateException Near OrderId: " + orderId;
                        logger.error(errMsg, e);
                                addActionError(errMsg);
                                
                        } catch(NumberFormatException e) {
                                String errMsg = "NumberFormatException for Extracted String: " + orderIdString;
                        logger.error(errMsg, e);
                                addActionError(errMsg);
                                
                        } catch (NullPointerException e) {
                                String errMsg = "NullPointerException For OrderId: " + orderId;
                        logger.error(errMsg, e);
                                addActionError(errMsg);
                                
                        } catch (TransactionServiceException e) {
                                String errMsg = "TransactionServiceException For OrderId: " + orderId;
                        logger.error(errMsg, e);
                                addActionError(errMsg);
                                } catch (Exception e) {
                                        String errMsg = "Exception recieved for OrderId:" + orderId;
                                        logger.error(errMsg, e);
                                        addActionError(errMsg);
                        }
                }
        } catch (ParseException e) {
                String errMsg = "Could not parse " + bluedartSettlementDate;
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                } catch (Exception e) {
                        String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
        }
        addActionMessage("Successfully added Bluedart settlement details for " + countSuccessfulSaves + " orders");
        }
        
        public void uploadAramexSettlements() {
                logger.info("Uploading Aramex Settlements" + this.aramexSettlementDate);
                
                if(this.aramexSettlementDate.equals("")) {
                        addActionError("Settlement date cannot be left blank");
                        return;
                }
                
                Workbook wb = null;
        try {
                wb = new XSSFWorkbook(new FileInputStream(this.aramexSettlementReport));
        } catch (FileNotFoundException e) {
                String errMsg = "Unable to open the Aramex Settlement report";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
        } catch (IOException e) {
                String errMsg = "Unable to open the Aramex Settlement report";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
        }
        
        Sheet sheet = wb.getSheetAt(0);
        
        int indexAWB = 0;
        int indexAmount = 1;
        int indexComment = 2;
        
        TransactionClient tsc;
        int countSuccessfulSaves = 0;
        try     {
                        long settlementDate = DATE_FORMAT.parse(aramexSettlementDate).getTime();
                        
                tsc = new TransactionClient();
                in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                
                for (Row row: sheet)    {
                        if(row.getRowNum() < 1) {
                                continue;
                        }
                        
                        if(row.getCell(indexAWB) == null) {
                                continue;
                        }
                        
                                long awb = -1;
                                
                                try {
                                        if(row.getCell(indexAWB).getCellType() == 0) {
                                                awb = (long) row.getCell(indexAWB).getNumericCellValue();
                                        }
                                    else if(row.getCell(indexAWB).getCellType() == 1) {
                                        awb = Long.parseLong(row.getCell(indexAWB).getStringCellValue());
                                    }
                                        
                                        if(awb == -1) {
                                                continue;
                                        }
                                else if(row.getCell(indexComment).getStringCellValue().equals("LOST SHIPMENT")) {
                                        logger.info("LOST SHIPMENT");
                                        continue;
                                }
                                        
                                long orderId = transactionClient.getOrderForAwb(Long.toString(awb)).get(0).getId();
                                double amount = row.getCell(indexAmount).getNumericCellValue();
                                logger.info("Aramex Settlement, Order Id: " + orderId + " Amount: " + amount);
                                
                                        transactionClient.savePaymentSettlements(settlementDate, 4, orderId, 0.0, 0.0, amount);
                                        countSuccessfulSaves += 1;
                                        
                                } catch (TransactionServiceException e) {
                                        logger.error("Transaction Service Exception while getting order for AWB, " + awb, e);
                                        addActionError("Transaction Service Exception while getting order for AWB, " + awb);    
                                } catch (Exception e) {
                                        String errMsg = "Exception recieved for AWB," + awb;
                                        logger.error(errMsg, e);
                                        addActionError(errMsg);
                                }
                }
        } catch (ParseException e) {
                String errMsg = "Could not parse " + aramexSettlementDate;
                logger.error(errMsg, e);
                        addActionError(errMsg);
        } catch (Exception e) {
                String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
        }
        addActionMessage("Successfully added Aramex settlement details for " + countSuccessfulSaves + " orders");
        }
        
        public void uploadDelhiverySettlements() {
                logger.info("Uploading Delhivery Settlements" + this.delhiverySettlementDate);
                
                if(this.delhiverySettlementDate.equals("")) {
                        addActionError("Settlement date cannot be left blank");
                        return;
                }
                
                Workbook wb = null;
        try {
                wb = new XSSFWorkbook(new FileInputStream(this.delhiverySettlementReport));
        } catch (FileNotFoundException e) {
                String errMsg = "Unable to open the Delhivery Settlement report";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
        } catch (IOException e) {
                String errMsg = "Unable to open the Delhivery Settlement report";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
        }
        
        Sheet sheet = wb.getSheetAt(0);
        
        int indexAWB = 1;
        int indexAmount = 13;
        
        TransactionClient tsc;
        int countSuccessfulSaves = 0;
        try     {
                        long settlementDate = DATE_FORMAT.parse(delhiverySettlementDate).getTime();
                        
                tsc = new TransactionClient();
                in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                
                for (Row row: sheet)    {
                        if(row.getRowNum() < 9) {
                                continue;
                        }
                        
                        if(row.getCell(indexAWB) == null) {
                                continue;
                        }
                        
                                long awb = -1;
                                
                                try {
                                        if(row.getCell(indexAWB).getCellType() == 0) {
                                                awb = (long) row.getCell(indexAWB).getNumericCellValue();
                                        }
                                    else if(row.getCell(indexAWB).getCellType() == 1) {
                                        awb = Long.parseLong(row.getCell(indexAWB).getStringCellValue());
                                    }
                                        
                                        if(awb == -1) {
                                                continue;
                                        }
                                        
                                long orderId = transactionClient.getOrderForAwb(Long.toString(awb)).get(0).getId();
                                double amount = row.getCell(indexAmount).getNumericCellValue();
                                logger.info("Delhivery Settlement, Order Id: " + orderId + " Amount: " + amount);
                                
                                        transactionClient.savePaymentSettlements(settlementDate, 4, orderId, 0.0, 0.0, amount);
                                        countSuccessfulSaves += 1;
                                        
                                } catch (TransactionServiceException e) {
                                        logger.error("Transaction Service Exception while getting order for AWB, " + awb, e);
                                        addActionError("Transaction Service Exception while getting order for AWB, " + awb);    
                                } catch (Exception e) {
                                        String errMsg = "Exception recieved for AWB," + awb;
                                        logger.error(errMsg, e);
                                        addActionError(errMsg);
                                }
                }
        } catch (ParseException e) {
                String errMsg = "Could not parse " + delhiverySettlementDate;
                logger.error(errMsg, e);
                        addActionError(errMsg);
        } catch (Exception e) {
                String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
        }
        addActionMessage("Successfully added Delhivery settlement details for " + countSuccessfulSaves + " orders");
        }
        
        public void uploadOrderIdsToBeMarkedPaid()      {
                logger.info("Uploading Order Ids to be marked as paid");
                List<Long> orderIds = new ArrayList<Long>();
                
                try {
                        FileInputStream uploadedFile = new FileInputStream(this.orderIdsToBeMarked);
                        String uploadedContent = IOUtils.toString(uploadedFile);
                        
                        for(String s: uploadedContent.trim().split("\n"))       {
                                
                                if(! s.trim().isEmpty()) {
                                        orderIds.add(Long.parseLong(s));
                                }
                        }
                } catch (FileNotFoundException e) {
                        String errMsg = "Unable to open the sheet containing orders for which vendor is paid";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
                } catch (IOException e) {
                        String errMsg = "IOException occured while reading the file containing orders for which vendor is paid";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
                }
                
        TransactionClient tsc;
        int countSuccessfulSaves = 0;
        
        try     {
                tsc = new TransactionClient();
                in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                
                for (long orderId: orderIds)    {
                        
                        logger.info("Marking as paid, Order Id: " + orderId);
                        try {
                                transactionClient.updateOrderOnlyAsPaidToVendor(orderId);
                                        countSuccessfulSaves += 1;
                                        
                                } catch (TransactionServiceException e) {
                                        logger.error("Transaction Service Exception while getting order for id, " + orderId, e);
                                        addActionError("Transaction Service Exception while getting order for id, " + orderId); 
                                }
                }
        } catch (Exception e) {
                        String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
        addActionMessage("Successfully marked " + countSuccessfulSaves + " orders as paid to vendor");
        }
        
        public void uploadOrderIdsToBeMarkedReturned()  {
                logger.info("Uploading Order Ids to be marked as Returned");
                List<Long> orderIds = new ArrayList<Long>();
                
                try {
                        FileInputStream uploadedFile = new FileInputStream(this.orderIdsToBeMarked);
                        String uploadedContent = IOUtils.toString(uploadedFile);
                        
                        for(String s: uploadedContent.trim().split("\n"))       {
                                
                                if(! s.trim().isEmpty()) {
                                        orderIds.add(Long.parseLong(s));
                                }
                        }
                } catch (FileNotFoundException e) {
                        String errMsg = "Unable to open the sheet containing returned orders to be marked";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
                } catch (IOException e) {
                        String errMsg = "IOException occured while reading the file containing returned orders to be marked";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                        return;
                }
                
        TransactionClient tsc;
        int countSuccessfulSaves = 0;
        
        try     {
                tsc = new TransactionClient();
                in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        
                for (long orderId: orderIds)    {
                        
                        logger.info("Marking as Returned, Order Id: " + orderId);
                        try {
                                transactionClient.processReturn(orderId);
                                        countSuccessfulSaves += 1;
                                        
                                } catch (TransactionServiceException e) {
                                        String errMsg = "Transaction Service Exception while getting order for id, " + orderId;
                                        logger.error(errMsg, e);
                                        addActionError(errMsg);
                                }
                }
                } catch (Exception e) {
                        String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
        addActionMessage("Successfully marked " + countSuccessfulSaves + " orders as returned to vendor");
        }
        
        private ByteArrayOutputStream getReconciliationReport(long vendorId, long fromTime, long toTime, boolean notall) {
                ByteArrayOutputStream baosXLS = new ByteArrayOutputStream();
                
                try {
                        
                        InventoryClient inventoryServiceClient = new InventoryClient();
            Client inventoryClient = inventoryServiceClient.getClient();
            List<Vendor> vendors = inventoryClient.getAllVendors();
            vendorIdNameMap = new Hashtable<Long, String>();
            for(Vendor vendor : vendors){
                vendorIdNameMap.put(vendor.getId(), vendor.getName());
            }
                        
                        TransactionClient tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        
                        List<Order> unpaidOrders = transactionClient.getBilledOrders(vendorId, notall, fromTime, toTime);
                        logger.info("Unpaid Order count: " + unpaidOrders.size());
                        
                        List<ReturnOrder> returns = new ArrayList<ReturnOrder>();
                        
                        try {
                                returns = transactionClient.getAllReturnOrders(notall, fromTime, toTime);
                        } catch (TTransportException e) {
                                tsc = new TransactionClient();
                                transactionClient = tsc.getClient();
                                returns = transactionClient.getAllReturnOrders(notall, fromTime, toTime);
                        }
                        
                        Map<Long, ReturnOrder> mapReturnOrderIds = new HashMap<Long, ReturnOrder>();
                        
                        for (ReturnOrder returnOrder: returns)
                        {
                                mapReturnOrderIds.put(returnOrder.getOrderId(), returnOrder);
                        }
                        
                        List<Long> ReturnOrderIds = new ArrayList<Long>(mapReturnOrderIds.keySet());
                        
                        List<Order> listofreturnedOrders = new ArrayList<Order>();
                        
                        try {
                                listofreturnedOrders = transactionClient.getOrderListForVendor(ReturnOrderIds, vendorId);
                        } catch (TTransportException e) {
                                tsc = new TransactionClient();
                                transactionClient = tsc.getClient();
                                listofreturnedOrders = transactionClient.getOrderListForVendor(ReturnOrderIds, vendorId);
                        }
                        
                        for (Order order: listofreturnedOrders)
                        {
                                order.setDelivery_timestamp(mapReturnOrderIds.get(order.getId()).getCreatedAt());
                                order.setVendorPaid(mapReturnOrderIds.get(order.getId()).isProcessedStatus());
                                returnedOrders.add(order);
                        }
                        
                        Set<Long> OriginalOrderIds = new HashSet<Long>();
                        Map<Long, Order> mapUnpaidOrders = new HashMap<Long, Order>();
                        
                        for (Order order: unpaidOrders)
                        {
                                if(order.getOriginalOrderId() == 0)     {
                                        OriginalOrderIds.add(order.getId());
                                }
                                else {
                                        OriginalOrderIds.add(order.getOriginalOrderId());
                                }
                                mapUnpaidOrders.put(order.getId(), order);
                        }
                        
                List<Order> reshippedPayableOrders = new ArrayList<Order>();
                List<Order> originalPayableOrders_user = new ArrayList<Order>();
                List<Order> originalPayableOrders_nouser = new ArrayList<Order>();
                
                Order originalOrder = null;
                        boolean originalOrderUnpaid = false;
                        Order tempOrder = null;
                        boolean tempOrderUnpaid = false;
                        long nextOrderId = 0;
                        String previous_invoice_number = "";
                
                        for (long originalOrderId: OriginalOrderIds)
                        {
                                originalOrder = null;
                                originalOrderUnpaid = false;
                                tempOrder = null;
                                tempOrderUnpaid = false;
                                nextOrderId = originalOrderId;
                                previous_invoice_number = "";
                                
                                while (true)
                                {
                                        if(mapUnpaidOrders.containsKey(nextOrderId)) {
                                                tempOrder = mapUnpaidOrders.get(nextOrderId);
                                                tempOrderUnpaid = true;
                                        }
                                        else {
                                                try {
                                                        tempOrder = transactionClient.getOrder(nextOrderId);
                                                } catch (TTransportException e) {
                                                        tsc = new TransactionClient();
                                                        transactionClient = tsc.getClient();
                                                        tempOrder = transactionClient.getOrder(nextOrderId);
                                                }
                                                tempOrderUnpaid = false;
                                        }
                                        
                                        if(tempOrderUnpaid && nextOrderId != originalOrderId) {
                                                if(previous_invoice_number.equals(tempOrder.getInvoice_number())) {
                                                        reshippedNonPayableOrders.add(tempOrder);
                                                }
                                                else {
                                                        reshippedPayableOrders.add(tempOrder);
                                                }
                                        }
                                        
                                        if(tempOrderUnpaid && nextOrderId == originalOrderId) {
                                                originalOrder = tempOrder;
                                                originalOrderUnpaid = true;
                                        }
                                        
                                        nextOrderId = tempOrder.getNew_order_id();
                                        previous_invoice_number = tempOrder.getInvoice_number();
                                        if(nextOrderId == 0) {
                                                if(originalOrderUnpaid) {
                                                        if(refundOrderStatuses.contains(tempOrder.getStatus())) {
                                                                originalPayableOrders_nouser.add(originalOrder);
                                                        }
                                                        else {
                                                                originalPayableOrders_user.add(originalOrder);
                                                        }
                                                }
                                                break;
                                        }
                                }
                        }
                        
                PaymentClient psc = new PaymentClient();
                in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
                        
                PaymentSettlement pmtSettlement = null;
                Payment payment = null;
                
                        for (Order UserpayRequiredOrder: originalPayableOrders_user)
                        {
                                try {
                                        pmtSettlement = null;
                                        
                                        if(UserpayRequiredOrder.isCod()) {
                                                try {
                                                        pmtSettlement = transactionClient.getSettlementForCod(UserpayRequiredOrder.getId(), false);
                                                } catch (TTransportException e) {
                                                        tsc = new TransactionClient();
                                                        transactionClient = tsc.getClient();
                                                        pmtSettlement = transactionClient.getSettlementForCod(UserpayRequiredOrder.getId(), false);
                                                }
                                                
                                                if(pmtSettlement.getReferenceId() > 0) {
                                                        paymentSettledOrdersMap.put(UserpayRequiredOrder, pmtSettlement);
                                                }
                                                else {
                                                        unsettledOrders.add(UserpayRequiredOrder);
                                                }
                                        }
                                        else {
                                                payment = null;
                                                
                                                try {
                                                        payment = paymentClient.getSuccessfulPaymentForTxnId(UserpayRequiredOrder.getTransactionId());
                                                } catch (TTransportException e) {
                                                        psc = new PaymentClient();
                                                paymentClient = psc.getClient();
                                                payment = paymentClient.getSuccessfulPaymentForTxnId(UserpayRequiredOrder.getTransactionId());
                                                }
                                                
                                                try {
                                                        pmtSettlement = transactionClient.getSettlementForPrepaid(payment.getPaymentId(), false);
                                                } catch (TTransportException e) {
                                                        tsc = new TransactionClient();
                                                        transactionClient = tsc.getClient();
                                                        pmtSettlement = transactionClient.getSettlementForPrepaid(payment.getPaymentId(), false);
                                                }
                                                
                                                if(pmtSettlement.getReferenceId() > 0) {
                                                        paymentSettledOrdersMap.put(UserpayRequiredOrder, pmtSettlement);
                                                }
                                                else {
                                                        unsettledOrders.add(UserpayRequiredOrder);
                                                }
                                        }
                                } catch (Exception e) {
                                        ordersWithErrors.add(UserpayRequiredOrder);
                                        String errMsg = "error occured in getReconciliationReport during " + UserpayRequiredOrder.getId();
                                        logger.error(errMsg, e);
                                        addActionError(errMsg);
                                }
                        }
                        
                        userPaymentNotRequiredOrders.addAll(originalPayableOrders_nouser);
                        logger.info("No User Payment Required Original Orders Count: " + originalPayableOrders_nouser.size());
                        userPaymentNotRequiredOrders.addAll(reshippedPayableOrders);
                        logger.info("Reshipped Payable Orders Count: " + reshippedPayableOrders.size());
                        logger.info("Payment Settlement Count: " + paymentSettledOrdersMap.keySet().size());
                        logger.info("Unsettled Orders Count: " + unsettledOrders.size());
                        logger.info("User Payment Not Required Orders Count: " + userPaymentNotRequiredOrders.size());
                        logger.info("Reshipped Orders Count: " + reshippedNonPayableOrders.size());
                        logger.info("Returned Orders Count: " + returnedOrders.size());
                        
                    Workbook wb = new HSSFWorkbook();
                        
                    Sheet totalPayableOrderSheet = wb.createSheet("Total Payable Orders");
                        Sheet settledOrderSheet = wb.createSheet("Settled Payable Orders");
                        Sheet unsettledOrderSheet = wb.createSheet("Unsettled Payable Orders");
                        Sheet returnedOrderSheet = wb.createSheet("Receivable Orders");
                        Sheet reshippedOrderSheet = wb.createSheet("Reshipped Non Payable Orders");
                        Sheet errorOrderSheet = wb.createSheet("Orders With Errors");
                        
                        populateSettledOrderSheet(totalPayableOrderSheet, true);
                    populateSettledOrderSheet(settledOrderSheet, false);
                        populateUnsettledOrderSheet(unsettledOrderSheet);
                        populateReturnedOrderSheet(returnedOrderSheet);
                    populateReshippedOrderSheet(reshippedOrderSheet);
                    populateErrorOrderSheet(errorOrderSheet);
                
                        wb.write(baosXLS);
                        baosXLS.close();
                        
                } catch (Exception e) {
                        String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
                
                return baosXLS;
        }
        
        private void populateErrorOrderSheet(Sheet sheet) {
                int rowCount = 0;
                
                Row headerRow = sheet.createRow(rowCount ++);
            headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
            headerRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue("Original Order Id");
            headerRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue("Vendor Id");
            headerRow.createCell(OrderReportColumn.VENDOR_NAME.getValue()).setCellValue("Vendor Name");
            headerRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue("Order Date");
            headerRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue("Billing Number");
            headerRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue("Billing Date");
            headerRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue("Returned Date");
            headerRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue("Brand");
            headerRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue("Model Name");
            headerRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue("Model Number");
            headerRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue("Color");
            headerRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue("Quantity");
            headerRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue("Unit Transfer Price");
            headerRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue("Unit Selling Price");
            headerRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue("Total Transfer Price");
            headerRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue("Total Selling Price");
            headerRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue("Current Status");
            headerRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue("Vendor Paid");
            
        try     {
                
                Row contentRow = null;
                        LineItem lineItem = null;
                
                        for (Order order: ordersWithErrors) 
                        {
                                contentRow = sheet.createRow(rowCount ++);
                                lineItem = order.getLineitems().get(0);
                                
                        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
                        if(order.getOriginalOrderId() == 0)     {
                                contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getId());
                                }
                                else {
                                        contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getOriginalOrderId());
                                }
                        contentRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue(order.getVendorId());
                        contentRow.createCell(OrderReportColumn.VENDOR_NAME.getValue()).setCellValue(vendorIdNameMap.get(order.getVendorId()));
                        contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
                            contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
                            contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
                            contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
                            contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
                            contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
                            contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
                            contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
                            contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
                            contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price());
                            contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
                            contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price() * lineItem.getQuantity());
                            contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(lineItem.getTotal_price());
                            contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatus().name());
                            contentRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue(order.isVendorPaid());
                }
            } catch (Exception e) {
                String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
        }
        
        private void populateReturnedOrderSheet(Sheet sheet) {
                int rowCount = 0;
                
                Row headerRow = sheet.createRow(rowCount ++);
            headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
            headerRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue("Original Order Id");
            headerRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue("Vendor Id");
            headerRow.createCell(OrderReportColumn.VENDOR_NAME.getValue()).setCellValue("Vendor Name");
            headerRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue("Order Date");
            headerRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue("Billing Number");
            headerRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue("Billing Date");
            headerRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue("Returned Date");
            headerRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue("Brand");
            headerRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue("Model Name");
            headerRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue("Model Number");
            headerRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue("Color");
            headerRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue("Quantity");
            headerRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue("Unit Transfer Price");
            headerRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue("Unit Selling Price");
            headerRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue("Total Transfer Price");
            headerRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue("Total Selling Price");
            headerRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue("Current Status");
            headerRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue("Processed");
            
        try     {
                
                Row contentRow = null;
                        LineItem lineItem = null;
                
                        for (Order order: returnedOrders) 
                        {
                                contentRow = sheet.createRow(rowCount ++);
                                lineItem = order.getLineitems().get(0);
                                
                        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
                        if(order.getOriginalOrderId() == 0)     {
                                contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getId());
                                }
                                else {
                                        contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getOriginalOrderId());
                                }
                        contentRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue(order.getVendorId());
                        contentRow.createCell(OrderReportColumn.VENDOR_NAME.getValue()).setCellValue(vendorIdNameMap.get(order.getVendorId()));
                        contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
                            contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
                            contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
                            contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
                            contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
                            contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
                            contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
                            contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
                            contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
                            contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price());
                            contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
                            contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price() * lineItem.getQuantity());
                            contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(lineItem.getTotal_price());
                            contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatus().name());
                            contentRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue(order.isVendorPaid());
                }
            } catch (Exception e) {
                String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
        }
        
        private void populateReshippedOrderSheet(Sheet sheet) {
                int rowCount = 0;
                
            Row headerRow = sheet.createRow(rowCount ++);
            headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
            headerRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue("Original Order Id");
            headerRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue("Vendor Id");
            headerRow.createCell(OrderReportColumn.VENDOR_NAME.getValue()).setCellValue("Vendor Name");
            headerRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue("Order Date");
            headerRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue("Billing Number");
            headerRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue("Billing Date");
            headerRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue("Delivery Date");
            headerRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue("Brand");
            headerRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue("Model Name");
            headerRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue("Model Number");
            headerRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue("Color");
            headerRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue("Quantity");
            headerRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue("Unit Transfer Price");
            headerRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue("Unit Selling Price");
            headerRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue("Total Transfer Price");
            headerRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue("Total Selling Price");
            headerRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue("Current Status");
            headerRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue("Vendor Paid");
            
        try     {
                
                Row contentRow = null;
                        LineItem lineItem = null;
                
                        for (Order order: reshippedNonPayableOrders) 
                        {
                                contentRow = sheet.createRow(rowCount ++);
                                lineItem = order.getLineitems().get(0);
                                
                        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
                        if(order.getOriginalOrderId() == 0)     {
                                contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getId());
                                }
                                else {
                                        contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getOriginalOrderId());
                                }
                        contentRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue(order.getVendorId());
                        contentRow.createCell(OrderReportColumn.VENDOR_NAME.getValue()).setCellValue(vendorIdNameMap.get(order.getVendorId()));
                        contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
                            contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
                            contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
                            if(order.getDelivery_timestamp() != 0)      {
                                contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
                                }
                            contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
                            contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
                            contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
                            contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
                            contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
                            contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price());
                            contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
                            contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price() * lineItem.getQuantity());
                            contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(lineItem.getTotal_price());
                            contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatusDescription());
                            contentRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue(order.isVendorPaid());
                }
            } catch (Exception e) {
                String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
        }
        
        private void populateUnsettledOrderSheet(Sheet sheet) {
                int rowCount = 0;
                
            Row headerRow = sheet.createRow(rowCount ++);
            headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
            headerRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue("Original Order Id");
            headerRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue("Vendor Id");
            headerRow.createCell(OrderReportColumn.VENDOR_NAME.getValue()).setCellValue("Vendor Name");
            headerRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue("Order Date");
            headerRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue("Billing Number");
            headerRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue("Billing Date");
            headerRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue("Delivery Date");
            headerRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue("Brand");
            headerRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue("Model Name");
            headerRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue("Model Number");
            headerRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue("Color");
            headerRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue("Quantity");
            headerRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue("Unit Transfer Price");
            headerRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue("Unit Selling Price");
            headerRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue("Total Transfer Price");
            headerRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue("Total Selling Price");
            headerRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue("Current Status");
            headerRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue("Vendor Paid");
            headerRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue("Payment Type");
            headerRow.createCell(OrderReportColumn.PAYMENT_STATUS.getValue()).setCellValue("Payment Status");
            headerRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue("Payment Id");
            headerRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue("Coupon Code");
            
        outputPaymentUnsettledOrdersRows(sheet, rowCount);
        }
        
        private int outputPaymentUnsettledOrdersRows(Sheet sheet, int rowCount) {
                
        try     {
                PaymentClient psc = new PaymentClient();
                in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
                
                TransactionClient tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        
                        Row contentRow = null;
                        LineItem lineItem = null;
                        Payment pmt = null;
                        Transaction txn = null;
                        
                        for (Order order: unsettledOrders)
                        {
                                contentRow = sheet.createRow(rowCount ++);
                                lineItem = order.getLineitems().get(0);
                                pmt = null;
                                txn = null;
                                
                                try     {
                                        if(order.isCod()) {
                                                try {
                                                        pmt = paymentClient.getPaymentForTxnId(order.getTransactionId()).get(0);
                                                } catch (TTransportException e) {
                                                        psc = new PaymentClient();
                                                paymentClient = psc.getClient();
                                                pmt = paymentClient.getPaymentForTxnId(order.getTransactionId()).get(0);
                                                }
                                                
                                                try {
                                                        txn = transactionClient.getTransaction(order.getTransactionId());
                                                } catch (TTransportException e) {
                                                        tsc = new TransactionClient();
                                                        transactionClient = tsc.getClient();
                                                        txn = transactionClient.getTransaction(order.getTransactionId());
                                                }
                                        }
                                        else {
                                                try {
                                                        pmt = paymentClient.getSuccessfulPaymentForTxnId(order.getTransactionId());
                                                } catch (TTransportException e) {
                                                        psc = new PaymentClient();
                                                paymentClient = psc.getClient();
                                                pmt = paymentClient.getSuccessfulPaymentForTxnId(order.getTransactionId());
                                                }
                                                
                                                try {
                                                        txn = transactionClient.getTransaction(pmt.getMerchantTxnId());
                                                } catch (TTransportException e) {
                                                        tsc = new TransactionClient();
                                                        transactionClient = tsc.getClient();
                                                        txn = transactionClient.getTransaction(pmt.getMerchantTxnId());
                                                }
                                        }
                                
                                contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
                                if(order.getOriginalOrderId() == 0)     {
                                        contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getId());
                                        }
                                        else {
                                                contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getOriginalOrderId());
                                        }
                                contentRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue(order.getVendorId());
                                contentRow.createCell(OrderReportColumn.VENDOR_NAME.getValue()).setCellValue(vendorIdNameMap.get(order.getVendorId()));
                                contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
                                    contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
                                    contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
                                    if(order.getDelivery_timestamp() != 0)      {
                                        contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
                                        }
                                    contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
                                    contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
                                    contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
                                    contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
                                    contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
                                    contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price());
                                    contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
                                    contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price() * lineItem.getQuantity());
                                    contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(lineItem.getTotal_price());
                                    contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatus().name());
                                    contentRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue(order.isVendorPaid());
                                    
                                    if(pmt.getGatewayId() == 4) {
                                        contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue("COD-" + codProviders.get(order.getLogistics_provider_id()));
                                    }
                                    else {
                                        contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue(paymentGateways.get(pmt.getGatewayId()));
                                    }
                                contentRow.createCell(OrderReportColumn.PAYMENT_STATUS.getValue()).setCellValue(pmt.getStatus().name());
                                contentRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue(pmt.getPaymentId());
                                contentRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue(txn.getCoupon_code());
                                
                                } catch (Exception e) {
                                        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
                                        String errMsg = "error occured in outputPaymentUnsettledOrdersRows during " + order.getId();
                                        logger.error(errMsg, e);
                                        addActionError(errMsg);
                                }
                }
            } catch (Exception e) {
                String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
            
            return rowCount;
        }
        
        private void populateSettledOrderSheet(Sheet sheet, boolean total) {
                int rowCount = 0;
                
            Row headerRow = sheet.createRow(rowCount ++);
            headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
            headerRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue("Original Order Id");
            headerRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue("Vendor Id");
            headerRow.createCell(OrderReportColumn.VENDOR_NAME.getValue()).setCellValue("Vendor Name");
            headerRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue("Order Date");
            headerRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue("Billing Number");
            headerRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue("Billing Date");
            headerRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue("Delivery Date");
            headerRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue("Brand");
            headerRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue("Model Name");
            headerRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue("Model Number");
            headerRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue("Color");
            headerRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue("Quantity");
            headerRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue("Unit Transfer Price");
            headerRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue("Unit Selling Price");
            headerRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue("Total Transfer Price");
            headerRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue("Total Selling Price");
            headerRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue("Current Status");
            headerRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue("Vendor Paid");
            headerRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue("Payment Type");
            headerRow.createCell(OrderReportColumn.PAYMENT_STATUS.getValue()).setCellValue("Payment Status");
            headerRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue("Payment Reference");
            headerRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue("Coupon Code");
            headerRow.createCell(OrderReportColumn.SETTLEMENT_DATE.getValue()).setCellValue("Settlement Date");
            headerRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue("Service Tax");
            headerRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue("Other Charges");
            headerRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue("Net Collection");
            headerRow.createCell(OrderReportColumn.USER_PAYABLE_AMOUNT.getValue()).setCellValue("User Payable Amount");
            
            rowCount = outputPaymentSettledOrdersRows(sheet, rowCount);
            rowCount = outputPaymentNotRequiredOrdersRows(sheet, rowCount);
            
            if(total) {
                rowCount = outputPaymentUnsettledOrdersRows(sheet, rowCount);
            }
        }
        
        private int outputPaymentSettledOrdersRows(Sheet sheet, int rowCount) {
                
                try     {
                        PaymentClient psc = new PaymentClient();
                in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
                
                TransactionClient tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        
                        Row contentRow = null;
                        Order order = null;
                        PaymentSettlement paymentSettlement = null;
                        Payment pmt = null;
                        Transaction txn = null;
                        LineItem lineItem = null;
                        
                for (Entry<Order, PaymentSettlement> entry : paymentSettledOrdersMap.entrySet())
                {
                        contentRow = sheet.createRow(rowCount ++);
                        order = entry.getKey();
                        paymentSettlement = entry.getValue();
                        pmt = null;
                        txn = null;
                        
                                try {
                                        if(order.isCod()) {
                                                try {
                                                        pmt = paymentClient.getPaymentForTxnId(order.getTransactionId()).get(0);
                                                } catch (TTransportException e) {
                                                        psc = new PaymentClient();
                                                paymentClient = psc.getClient();
                                                pmt = paymentClient.getPaymentForTxnId(order.getTransactionId()).get(0);
                                                }
                                                
                                                try {
                                                        txn = transactionClient.getTransaction(order.getTransactionId());
                                                } catch (TTransportException e) {
                                                        tsc = new TransactionClient();
                                                        transactionClient = tsc.getClient();
                                                        txn = transactionClient.getTransaction(order.getTransactionId());
                                                }
                                        }
                                        else {
                                                try {
                                                        pmt = paymentClient.getSuccessfulPaymentForTxnId(order.getTransactionId());
                                                } catch (TTransportException e) {
                                                        psc = new PaymentClient();
                                                paymentClient = psc.getClient();
                                                pmt = paymentClient.getSuccessfulPaymentForTxnId(order.getTransactionId());
                                                }
                                                
                                                try {
                                                        txn = transactionClient.getTransaction(pmt.getMerchantTxnId());
                                                } catch (TTransportException e) {
                                                        tsc = new TransactionClient();
                                                        transactionClient = tsc.getClient();
                                                        txn = transactionClient.getTransaction(pmt.getMerchantTxnId());
                                                }
                                        }
                                lineItem = order.getLineitems().get(0);
                                        
                                contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
                                if(order.getOriginalOrderId() == 0)     {
                                        contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getId());
                                        }
                                        else {
                                                contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getOriginalOrderId());
                                        }
                                contentRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue(order.getVendorId());
                                contentRow.createCell(OrderReportColumn.VENDOR_NAME.getValue()).setCellValue(vendorIdNameMap.get(order.getVendorId()));
                                contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
                                    contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
                                    contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
                                    if(order.getDelivery_timestamp() != 0)      {
                                        contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
                                        }
                                    contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
                                    contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
                                    contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
                                    contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
                                    contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
                                    contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price());
                                    contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
                                    contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price() * lineItem.getQuantity());
                                    contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(lineItem.getTotal_price());
                                    contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatus().name());
                                    contentRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue(order.isVendorPaid());
                                
                                        if(pmt.getGatewayId() == 4)     {
                                        contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue("COD-" + codProviders.get(order.getLogistics_provider_id()));
                                        contentRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue(paymentSettlement.getServiceTax());
                                            contentRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue(paymentSettlement.getOtherCharges());
                                            contentRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue(paymentSettlement.getNetCollection());
                                            contentRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue(paymentSettlement.getReferenceId());
                                    }
                                    else {
                                        contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue(paymentGateways.get(pmt.getGatewayId()));
                                        contentRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue((paymentSettlement.getServiceTax() * order.getTotal_amount()) / pmt.getAmount());
                                            contentRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue((paymentSettlement.getOtherCharges() * order.getTotal_amount()) / pmt.getAmount());
                                            contentRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue((paymentSettlement.getNetCollection() * order.getTotal_amount()) / pmt.getAmount());
                                            contentRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue(pmt.getPaymentId());
                                    }
                                contentRow.createCell(OrderReportColumn.PAYMENT_STATUS.getValue()).setCellValue(pmt.getStatus().name());
                                contentRow.createCell(OrderReportColumn.SETTLEMENT_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(paymentSettlement.getSettlementDate())));
                                    contentRow.createCell(OrderReportColumn.USER_PAYABLE_AMOUNT.getValue()).setCellValue(order.getTotal_amount());
                                    contentRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue(txn.getCoupon_code());
                            
                        } catch (Exception e) {
                                        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
                                        String errMsg = "error occured in outputPaymentSettledOrdersRows during " + order.getId();
                                        logger.error(errMsg, e);
                                        addActionError(errMsg);
                                }
                        }
                } catch (Exception e) {
                        String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
                
                return rowCount;
        }
        
        private int outputPaymentNotRequiredOrdersRows(Sheet sheet, int rowCount) {
                
                try     {
                        
                        Row contentRow = null;
                        LineItem lineItem = null;
                        
                        for (Order order: userPaymentNotRequiredOrders)
                        {
                                contentRow = sheet.createRow(rowCount ++);
                                lineItem = order.getLineitems().get(0);
                                
                        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
                        if(order.getOriginalOrderId() == 0)     {
                                contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getId());
                                }
                                else {
                                        contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getOriginalOrderId());
                                }
                        contentRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue(order.getVendorId());
                        contentRow.createCell(OrderReportColumn.VENDOR_NAME.getValue()).setCellValue(vendorIdNameMap.get(order.getVendorId()));
                        contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
                            contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
                            contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
                            if(order.getDelivery_timestamp() != 0)      {
                                contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
                                }
                            contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
                            contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
                            contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
                            contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
                            contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
                            contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price());
                            contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
                            contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price() * lineItem.getQuantity());
                            contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(lineItem.getTotal_price());
                            contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatus().name());
                            contentRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue(order.isVendorPaid());
                            contentRow.createCell(OrderReportColumn.USER_PAYABLE_AMOUNT.getValue()).setCellValue(0);
                        }
                } catch (Exception e) {
                        String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
                
                return rowCount;
        }
        
        private void initiatializeDashboard()   {
                try     {
                        OrderStatusGroups orderStatusGroups = new OrderStatusGroups();
                        refundOrderStatuses = orderStatusGroups.getRefundedOrders();
                        
                        InventoryClient csc = new InventoryClient();
                        in.shop2020.model.v1.inventory.InventoryService.Client inventoryClient = csc.getClient();
                        vendors = inventoryClient.getAllVendors();
                        
                        TransactionClient tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        ebsSettlementSummaries = transactionClient.getEBSSettlementSummaries();
                        
                } catch (Exception e) {
                        String errMsg = e.getMessage();
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
        }
        
        public List<Vendor> getAllVendors()     {
                return this.vendors;
        }

        private String getValueForEmptyString(String s){
                if(s==null || s.equals(""))
                        return "-";
                else
                        return s; 
        }
        
        private String getVendorName(long vendorId)     {
                for (Vendor vendor: vendors)    {
                        if (vendor.getId() == vendorId) return vendor.getName();
                }
                return null;
        }
        
        public File getHdfcSettlementReport() {
        return hdfcSettlementReport;
    }

    public void setHdfcSettlementReport(File hdfcSettlementReport) {
        this.hdfcSettlementReport = hdfcSettlementReport;
    }
    
    public File getEbsSettlementSummary()       {
        return ebsSettlementSummary;
    }
    
    public void setEbsSettlementSummary(File ebsSettlementSummary)      {
        this.ebsSettlementSummary = ebsSettlementSummary;
    }

    public File getEbsSettlementReport()        {
        return ebsSettlementReport;
    }
    
    public void setEbsSettlementReport(File ebsSettlementReport)        {
        this.ebsSettlementReport = ebsSettlementReport;
    }
    
    public String getBluedartSettlementDate()   {
        return bluedartSettlementDate;
    }
    
    public void setBluedartSettlementDate(String bluedartSettlementDate)        {
        this.bluedartSettlementDate = bluedartSettlementDate;
    }
    
    public File getBluedartSettlementReport()   {
        return bluedartSettlementReport;
    }
    
    public void setBluedartSettlementReport(File bluedartSettlementReport)      {
        this.bluedartSettlementReport = bluedartSettlementReport;
    }
    
    public String getAramexSettlementDate()     {
        return this.aramexSettlementDate;
    }
    
    public void setAramexSettlementDate(String aramexSettlementDate)    {
        this.aramexSettlementDate = aramexSettlementDate;
    }
    
    public void setAramexSettlementReport(File aramexSettlementReport) {
                this.aramexSettlementReport = aramexSettlementReport;
        }
    
        public File getAramexSettlementReport() {
                return aramexSettlementReport;
        }
        
        public String getDelhiverySettlementDate()      {
        return this.delhiverySettlementDate;
    }
    
    public void setDelhiverySettlementDate(String delhiverySettlementDate)      {
        this.delhiverySettlementDate = delhiverySettlementDate;
    }
    
    public void setDelhiverySettlementReport(File delhiverySettlementReport) {
                this.delhiverySettlementReport = delhiverySettlementReport;
        }
    
        public File getDelhiverySettlementReport() {
                return delhiverySettlementReport;
        }
        
        @Override
    public void setServletContext(ServletContext context) {
        this.context = context;
    }

    public String getServletContextPath() {
        return context.getContextPath();
    }

        @Override
        public void setServletResponse(HttpServletResponse response) {
                this.response = response;
        }

        @Override
        public void setServletRequest(HttpServletRequest request) {
                this.request = request;
        }
        
        public Map<Long, String> getEBSSettlementSummaries() {
                return ebsSettlementSummaries;
        }
        
        public void setOrderIdsToBeMarked(File orderIdsToBeMarked) {
                this.orderIdsToBeMarked = orderIdsToBeMarked;
        }

        public File getOrderIdsToBeMarked() {
                return orderIdsToBeMarked;
        }
}