Subversion Repositories SmartDukaan

package in.shop2020.support.controllers;

import in.shop2020.logistics.LogisticsServiceException;
import in.shop2020.logistics.Provider;
import in.shop2020.model.v1.catalog.InventoryService.Client;
import in.shop2020.model.v1.catalog.Vendor;
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.Transaction;
import in.shop2020.model.v1.order.TransactionServiceException;
import in.shop2020.payments.Payment;
import in.shop2020.payments.PaymentException;
import in.shop2020.payments.PaymentGateway;
import in.shop2020.thrift.clients.CatalogClient;
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.LinkedList;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
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.TApplicationException;
import org.apache.thrift.TException;
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
 */
//
//@InterceptorRefs({
//    @InterceptorRef("defaultStack"),
//    @InterceptorRef("login")
//})
//@Results({
//    @Result(name="authfail", type="redirectAction", params = {"actionName" , "reports"})
//})
public class VendorReconciliationController extends ValidationAwareSupport implements ServletRequestAware, ServletResponseAware, ServletContextAware {

    private static Logger logger = LoggerFactory.getLogger(VendorReconciliationController.class);
    
        private enum OrderReportColumn {
            ORDER_ID(0),
            ORDER_DATE(1),
                BILLING_NUMBER(2),
                BILLING_DATE(3),
                DELIVERY_DATE(4),
                SETTLEMENT_DATE(5),
                CURRENT_STATUS(6),
                PAYMENT_STATUS(7),
                BRAND(7),
                MODEL_NAME(8),
                MODEL_NUMBER(9),
                COLOR(10),
                QUANTITY(11),
                UNIT_TRANSFER_PRICE(12),
                UNIT_SELLING_PRICE(13),
                TOTAL_TRANSFER_PRICE(14),
                TOTAL_SELLING_PRICE(15),
                PAYMENT_TYPE(16),
                PAYMENT_ID(17),
                COUPON_CODE(18),
                SERVICE_TAX(19),
                OTHER_CHARGES(20),
                NET_COLLECTION(21),
            REFUND(22),
            FINAL_AMOUNT(23),
            IS_RESHIPED_ORDER(24);
//              COUPON_CATEGORY(19),
//              DISCOUNT(20),
            
                private int value;
                
                OrderReportColumn(int value) {
                        this.value = value;
                }
                public int getValue(){
                        return this.value;
                }
        }
        
        private enum OrderMismatchColumn        {
                ORDER_ID(0),
                EXPECTED_COLLECTION(1),
                ACTUAL_COLLECTION(2);

                private int value;
                
                OrderMismatchColumn(int value) {
                        this.value = value;
                }
                public int getValue(){
                        return this.value;
                }
        }
        
        private HttpServletRequest request;
        private HttpServletResponse response;
        private ServletContext context;
    private HttpSession session;
        private List<Vendor> vendors;
        private Map<Long, String> codProviders = new HashMap<Long, String>();
        private Map<Long, String> paymentGateways = new HashMap<Long, String>();
        
        private String reportSource;
        private List<Long> orderIdsWithoutSuccess;
        
        private File orderIdsToBeMarkedPaid;
        private File hdfcSettlementReport;
        
        private File ebsSettlementReport;
        private File ebsSettlementSummary;
        
        private String bluedartSettlementDate;
        private File bluedartSettlementReport;
        
        private String aramexSettlementDate;
        private File aramexSettlementReport;
        
        private Map<Long, Map<String, Double>> misMatches = new HashMap<Long, Map<String, Double>>();
        private Map<Long, String> ebsSettlementSummaries;
        
        private List<OrderStatus> refundOrderStatuses;

        private List<Order> reshippedOrders = new ArrayList<Order>();
        private List<Order> refundedOrders = new ArrayList<Order>();
        private List<Order> unsettledOrders = new ArrayList<Order>();
        
        private Map<Long, PaymentSettlement> paymentSettlementsMap = new HashMap<Long, PaymentSettlement>();
        
        private final DateFormat DATE_FORMAT = new SimpleDateFormat("dd/MM/yyyy");
        
        private int rowCount = 0;
        
        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();
                }
            logger.info("Order Ids where no successful payment was found", orderIdsWithoutSuccess);

                initiatializeDashboard();
                return new DefaultHttpHeaders("report");
        }
        
        public HttpHeaders generateReconciliationReport()       {
                DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
                initiatializeDashboard();
                try     {
                        //Formatting Form input parameters
                        
//                  Date startDate = dateFormat.parse(request.getParameter("start"));
//                      Date endDate = dateFormat.parse(request.getParameter("end"));
                    long vendorId = Long.parseLong(request.getParameter("vendor"));
                        
                    logger.info("Vendor ID: " + vendorId);
                        
                    String vendorName = getVendorName(vendorId);
                        DateFormat dateFormatForFile = new SimpleDateFormat("dd.MM.yyyy");
                        
                        response.setContentType("application/vnd.ms-excel");
                        response.setHeader("Content-disposition", "inline; filename=" + vendorName + "-reconciliation.xls");
                        
                        ServletOutputStream sos;
                        try {
                                ByteArrayOutputStream baos = getReconciliationReport(vendorId);
                                sos = response.getOutputStream();
                                baos.writeTo(sos);
                                sos.flush();
                        } catch (IOException e) {
                                logger.error("Error while streaming the hotspot reconciliation report", e);
                        }
                } catch (NullPointerException e)   {
            logger.error("NullPointerException", e);
                }
                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) {
            logger.error("Unable to open the Settlement Summary report", e);
        } catch (IOException e) {
            logger.error("Unable to open the Settlement Summary report", e);
        }
        Sheet sheet = wb.getSheetAt(0);
        
        int indexSettlementId = 0;
        int indexSettlementDate = 1;
        int indexTransactionDateFrom = 2;
        int indexTransactionDateTo = 3;
        int indexAmount = 4;
        
        Row firstRow = sheet.getRow(0);
        Row secondRow = sheet.getRow(1);
        Row thirdRow = sheet.getRow(2);
        Row fourthRow = sheet.getRow(3);
        Row fifthRow = sheet.getRow(4);
        
                TransactionClient tsc;
                
                try {
                        tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        
                        for(Row row: sheet)     {
                                if(row.equals(firstRow) || row.equals(secondRow) || row.equals(thirdRow) || row.equals(fourthRow) || row.equals(fifthRow))      {
                                        continue;
                                }
                                long 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);
                                
                                try     {
                                        transactionClient.saveEBSSettlementSummary(settlementId, settlementDate.getTime(), transactionDateFrom.getTime(), transactionDateTo.getTime(), amount);
                                        
                                } catch (Exception e) {
                                        e.printStackTrace();
                                }
                        }
        } catch (Exception e) {
                e.printStackTrace();
                }
        }
        
        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);
                
                Row firstRow = sheet.getRow(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.equals(firstRow))        continue;
                
                if(row.getCell(0).getStringCellValue().equals("Total")) break;
                
                try     {
                    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, " + paymentId;
                                logger.error(errMsg + e);
                                addActionError(errMsg);
                        }
                        }
                } catch (TTransportException e) {
                        logger.error("TTransportException recieved for payment Id," + Long.toString(paymentId) + " " + e);
                
                } catch (TException e) {
                        logger.error("TException recieved for payment Id," + Long.toString(paymentId) + " " + e);
                }
                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) {
            logger.error("Unable to open the EBS Settlement detail report", e);
        } catch (IOException e) {
            logger.error("Unable to open the EBS Settlement detail report", e);
        }
        
        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 (TTransportException e) {
                logger.error("TTransportException " + e);
                } catch (TException e) {
                logger.error("TException " + e);
                }
        }
        
        public void uploadBluedartSettlements() {
                
                Workbook wb = null;
        try {
            wb = new HSSFWorkbook(new FileInputStream(this.bluedartSettlementReport));
        } catch (FileNotFoundException e) {
            logger.error("Unable to open the Settlement report", e);
        } catch (IOException e) {
            logger.error("Unable to open the Settlement report", e);
        }
        
        Sheet sheet = wb.getSheetAt(0);
        
        Row firstRow = sheet.getRow(0);
        logger.info("Cell count:" + firstRow.getPhysicalNumberOfCells());
        
        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();
                
                for (Row row : sheet) {
                        long orderId = 0;
                        String orderIdString = "";
                        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) {
                                logger.error("" + e);
                                
                        } catch(NumberFormatException e)        {
                                logger.error("Extracted String: " + orderIdString + " " + e);
                                
                        } catch (NullPointerException e) {
                                logger.error("For OrderId: " + orderId + " " + e);
                                
                        } catch (TransactionServiceException e) {
                                        logger.error("For OrderId: " + orderId + " " + e);
                                }
                }
        } catch (ParseException e) {
                logger.error("Could not parse " + bluedartSettlementDate + " " + e);
                        addActionError(e.getMessage());
                        
                } catch (TTransportException e) {
                        logger.error("" + e);
                        addActionError(e.getMessage());
                        
        } catch (TException e) {
                logger.error("" + e);
                        addActionError(e.getMessage());
        }
        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) {
                addActionError(e.getMessage());
            logger.error("Unable to open the Settlement report", e);
        } catch (IOException e) {
                addActionError(e.getMessage());
            logger.error("Unable to open the Settlement report", e);
        } catch (NullPointerException e) {
                addActionError(e.getMessage());
                logger.error("Unable to open the Settlement report", e);
                return;
                }
        
        Sheet sheet = wb.getSheetAt(0);
        
        int indexAWB = 0;
        int indexAmount = 1;
        int indexComment = 2;
        
        Row firstRow = sheet.getRow(0);
        logger.info("Cell count:" + firstRow.getPhysicalNumberOfCells());
        
        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.equals(firstRow)) {
                                continue;
                        }
                        
                                long awb = -1;
                                
                                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;
                        }
                                
                        try {
                                long orderId = transactionClient.getOrderForAwb(Long.toString(awb)).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 (ParseException e) {
                addActionError("Could not parse " + aramexSettlementDate);
                logger.error("Could not parse " + aramexSettlementDate + " " + e);
                
        } catch (TTransportException e) {
                addActionError(e.getMessage());
                logger.error("" + e);
                
        } catch (TException e) {
                addActionError(e.getMessage());
                logger.error("" + e);
        }
        addActionMessage("Successfully added Aramex 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.orderIdsToBeMarkedPaid);
                        String uploadedContent = IOUtils.toString(uploadedFile);
                        
                        for(String s: uploadedContent.trim().split("\n"))       {
                                
                                if(! s.trim().isEmpty())        orderIds.add(Long.parseLong(s));
                        }
                } catch (FileNotFoundException e) {
                        addActionError(e.getMessage());
            logger.error("Unable to open the sheet containing orders for which vendor is paid", e);
                } catch (IOException e) {
                        addActionError(e.getMessage());
            logger.error("IOException occured while reading the file containing orders for which vendor is paid", e);
                }
                
        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.updateOrderAsPaidToVendor(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 (TTransportException e) {
                addActionError(e.getMessage());
                logger.error("" + e);
                
        } catch (TException e) {
                addActionError(e.getMessage());
                logger.error("" + e);
        }
        addActionMessage("Successfully marked " + countSuccessfulSaves + " orders as paid to vendor");
        }
        
        private ByteArrayOutputStream getReconciliationReport(long vendorId)    {
                
                try     {
                        TransactionClient tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();

                PaymentClient psc = new PaymentClient();
                in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
                
                        List<Order> unpaidOrders = transactionClient.getOrdersWhereVendorNotPaid(vendorId);
                        logger.info("Unpaid Order count: " + unpaidOrders.size());
                        
                        Map<Long, List<Order>> mapAllOrders = new HashMap<Long, List<Order>>();
                        
                        for(Order order: unpaidOrders)  {
                                
                                if(order.getOriginalOrderId() == 0)     {
                                        List<Order> l = new LinkedList<Order>();
                                        l.add(order);
                                        mapAllOrders.put(order.getId(), l);
                                        
                                } else if(mapAllOrders.containsKey(order.getOriginalOrderId())) {
                                        
                                        List<Order> l = mapAllOrders.get(order.getOriginalOrderId());
                                        l.add(order);
                                        mapAllOrders.put(order.getOriginalOrderId(), l);
                                        
                                } else  {
                                        List<Order> l = new LinkedList<Order>();
                                        l.add(order);
                                        mapAllOrders.put(order.getOriginalOrderId(), l);
                                }
                        }
                        logger.info("Unpaid Order count in Map: " + mapAllOrders.keySet().size());
                        
                        for (long originalOrderId: mapAllOrders.keySet())       {
                                List <Order> orders = mapAllOrders.get(originalOrderId);
                                
                                try {
                                        PaymentSettlement pmtSettlement = null;
                                        
                                        if(orders.get(0).isCod())       {       // COD Order(s)
                                                long settledOrderId = 0;
                                                
                                                for (Order order: orders)       {
                                                        pmtSettlement = transactionClient.getSettlementForReferenceId(order.getId(), 4, false);
                                                        
                                                        if(pmtSettlement.getReferenceId() > 0)  {
                                                                settledOrderId = order.getId();
                                                                paymentSettlementsMap.put(order.getId(), pmtSettlement);
                                                        }
                                                }
                                                
                                                if(settledOrderId > 0)  {       // Adding unsettled clone orders to re-shipped orders
                                                        for (Order order: orders)       {
                                                                if (order.getId() != settledOrderId)    {
                                                                        reshippedOrders.add(order);
                                                                }
                                                        }
                                                } else  {
                                                        unsettledOrders.addAll(orders);
                                                }
                                        } else  {                                               // Prepaid Order(s)
                                                Payment payment = paymentClient.getSuccessfulPaymentForTxnId(orders.get(0).getTransactionId());
                                                
                                                // Checking if this payment settlement has already been fetched
                                                if(! paymentSettlementsMap.containsKey(payment.getPaymentId())) {
                                                        
                                                        pmtSettlement = transactionClient.getSettlementForReferenceId(payment.getPaymentId(), payment.getGatewayId(), false);
                                                        
                                                        if(pmtSettlement.getReferenceId() > 0)  {
                                                                paymentSettlementsMap.put(payment.getPaymentId(), pmtSettlement);
                                                        } else  {
                                                                unsettledOrders.addAll(orders);
                                                        }
                                                }
                                                
                                                if(orders.size() > 1)   {
                                                        reshippedOrders.addAll(orders.subList(1, orders.size()));
                                                }
                                        }
                                } catch (PaymentException e) {
                                        logger.error("" + e);
                                }
                        }
                        logger.info("Reshipped Orders count: " + reshippedOrders.size());
                        
                } catch (TransactionServiceException e) {
                        String errMsg = "Transaction Service Exception occured";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                } catch (TTransportException e) {
                        String errMsg = "Transaction Service Exception occured";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                } catch (TException e) {
                        // TODO Auto-generated 
                        String errMsg = "Transaction Service Exception occured";
                        logger.error(errMsg, e);
                        addActionError(errMsg);
                }
                logger.info("Payment Settlement Count: " + paymentSettlementsMap.keySet().size());
                logger.info("Payment Settlements: " + paymentSettlementsMap.keySet());
                logger.info("Unsettled Orders Count: " + unsettledOrders.size());
                
                ByteArrayOutputStream baosXLS = new ByteArrayOutputStream();
                
            Workbook wb = new HSSFWorkbook();
                
            //Create the style for the title row
            Font font = wb.createFont();
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            CellStyle boldStyle = wb.createCellStyle();
            boldStyle.setFont(font);
            
            CellStyle rightAlignStyle = wb.createCellStyle();
            rightAlignStyle.setAlignment(CellStyle.ALIGN_RIGHT);

                Sheet orderSheet = wb.createSheet("Settled Orders");
                Sheet unsettledOrderSheet = wb.createSheet("Unsettled Orders");
                Sheet reshippedOrderSheet = wb.createSheet("Reshipped");
//              Sheet mismatchSheet = wb.createSheet("Mismatches");
                
            populateOrderSheet(vendorId, orderSheet, boldStyle, rightAlignStyle);
                populateUnsettledOrderSheet(unsettledOrderSheet);
            populateReshippedOrderSheet(reshippedOrders, reshippedOrderSheet);
//          populateMismatchSheet(mismatchSheet);
        
                // Write the workbook to the output stream
                try {
                        wb.write(baosXLS);
                        baosXLS.close();
                } catch (IOException e) {
                        logger.error("Unable to write the hotspot reconciliation report to the byte array", e);
                }
                return baosXLS;
        }
        
        private void populateMismatchSheet(Sheet sheet) {
            int serialNo = 0;
                Row titleRow = sheet.createRow(serialNo ++);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellValue("Order Reconciliation Report (Mis-matches)");
            
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
            sheet.createRow(serialNo ++);
            
            Row headerRow = sheet.createRow(serialNo ++);
            headerRow.createCell(OrderMismatchColumn.ORDER_ID.getValue()).setCellValue("Order Id");
            headerRow.createCell(OrderMismatchColumn.EXPECTED_COLLECTION.getValue()).setCellValue("Expected Collection");
            headerRow.createCell(OrderMismatchColumn.ACTUAL_COLLECTION.getValue()).setCellValue("Actual Collection");
            
            for (long orderId: misMatches.keySet())     {
                Row contentRow = sheet.createRow(serialNo ++);
                contentRow.createCell(OrderMismatchColumn.ORDER_ID.getValue()).setCellValue(orderId);
                contentRow.createCell(OrderMismatchColumn.EXPECTED_COLLECTION.getValue()).setCellValue(misMatches.get(orderId).get("expected"));
                contentRow.createCell(OrderMismatchColumn.ACTUAL_COLLECTION.getValue()).setCellValue(misMatches.get(orderId).get("actual"));
            }
        }
        
        private void populateReshippedOrderSheet(List<Order> orders, Sheet sheet) {
            short serialNo = 0;
            
            // Create the title row and put all the titles in it. Rows are 0 based.
            Row titleRow = sheet.createRow(serialNo ++);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellValue("Reshipped Orders");
            
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
            
            sheet.createRow(serialNo ++);
            
            Row headerRow = sheet.createRow(serialNo ++);
            headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
            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.SETTLEMENT_DATE.getValue()).setCellValue("Original Order Id");
            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.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");
//          headerRow.createCell(OrderReportColumn.COUPON_CATEGORY.getValue()).setCellValue("Coupon Category");
//          headerRow.createCell(OrderReportColumn.DISCOUNT.getValue()).setCellValue("Discount");
            
            sheet.createRow(serialNo ++);
            
                TransactionClient tsc;
        PaymentClient psc;
        LogisticsClient lsc;
        
                try {
                        tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        
                        psc = new PaymentClient();
                in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
                
                lsc = new LogisticsClient();
                in.shop2020.logistics.LogisticsService.Client logisticsClient = lsc.getClient();
                
                Map<Long, String> codProviders = new HashMap<Long, String>();
                
                for (Provider provider: logisticsClient.getAllProviders())      {
                        codProviders.put(provider.getId(), provider.getName());
                }
                
                List<PaymentGateway> tPaymentGateways = paymentClient.getActivePaymentGateways();
                Map<Long, String> paymentGateways = new HashMap<Long, String>();
                
                for (PaymentGateway tPaymentGateway: tPaymentGateways)  {
                        paymentGateways.put(tPaymentGateway.getId(), tPaymentGateway.getName());
                }
                
                        for (Order order: orders)       {
                            logger.info("For order: " + order.getId());
                        
                            LineItem lineItem = order.getLineitems().get(0);
                            double transferPrice = lineItem.getTransfer_price();
                            Payment payment = null;
                            
                            try {
                                if(order.isCod())       {
                                        payment = paymentClient.getPaymentForTxnId(order.getTransactionId()).get(0);
                                } else  {
                                        payment = paymentClient.getSuccessfulPaymentForTxnId(order.getTransactionId());
                                }
                                    logger.info("Getting Successful Payment for Txn Id: " + order.getTransactionId());
                                    
                            } catch (TApplicationException e)   {
                                orderIdsWithoutSuccess.add(order.getId());
                                String errMsg = "Exception while getting successfull payment for transaction Id, " + order.getTransactionId() + "(Order: " + order.getId() + ")"; 
                                        logger.error(errMsg, e);
                                        addActionError(errMsg);
                                }
                            
                            Transaction txn = transactionClient.getTransaction(order.getTransactionId());
                            PaymentSettlement paymentSettlement = null;
                            String paymentType = null;
                            
                            if(payment != null) {
//                              paymentSettlement = transactionClient.getSettlementForPaymentId(payment.getPaymentId());
//                              paymentType = paymentGateways.get(payment.getGatewayId());
                            }
                            
                    double totalSellingPrice = lineItem.getTotal_price();
                    double serviceTax = 0.0;
                    double otherCollectionCharges = 0.0;
                    double netCollection = 0.0;
                    
                    if(paymentSettlement != null)       {
                            serviceTax = paymentSettlement.getServiceTax();
                            otherCollectionCharges = paymentSettlement.getOtherCharges();
                            netCollection = paymentSettlement.getNetCollection();
                    }
                    
                    if (netCollection < 0.0)    {
                        paymentType = paymentType + "-REFUND";
                    }
                    
                        Row contentRow = sheet.createRow(serialNo++);
                    contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
                    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.SETTLEMENT_DATE.getValue()).setCellValue(order.getOriginalOrderId());
                            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(transferPrice);
                            contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
                            contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(transferPrice * lineItem.getQuantity());
                            contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(totalSellingPrice);
                            contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatusDescription());

                        double refund = 0.0;
                        
                        if(order.isCod())       {
                                contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue("COD - " + codProviders.get(order.getLogistics_provider_id()));
                        } else  {
                                contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue(paymentType);
                        }
                        
                            if (payment != null)        {
//                              contentRow.createCell(OrderReportColumn.PAYMENT_STATUS.getValue()).setCellValue(payment.getStatus().name());
//                              contentRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue(payment.getPaymentId());
                            }
                            contentRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue(txn.getCoupon_code());
                        }
                        
                    logger.info("Order Ids where no successful payment was found: " + orderIdsWithoutSuccess.size());
                    logger.info(orderIdsWithoutSuccess.toString());
                    
                sheet.createRow(serialNo++);
                
                } catch (TTransportException e) {
                        logger.error("TTransportException " + e);
                } catch (PaymentException e) {
                        logger.error("PaymentException " + e);
                } catch (TException e) {
                        logger.error("TException " + e);
                } catch (TransactionServiceException e) {
                        logger.error("Transaction Service Exception " + e);
                } catch (LogisticsServiceException e) {
                        addActionError("Error loading list of COD providers");
                        logger.error("Logistics Service Exception " + e);
                }
        }
        
        private void populateUnsettledOrderSheet(Sheet sheet)   {
                rowCount = 0;
                
            Row headerRow = sheet.createRow(rowCount ++);
            headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
            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.SETTLEMENT_DATE.getValue()).setCellValue("Settlement 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.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");
            
            sheet.createRow(rowCount ++);

        PaymentClient psc;
        logger.info("unsettledOrders count: " + unsettledOrders.size());
        
        try     {
                        psc = new PaymentClient();
                in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
                        
                        for (Order order: unsettledOrders)      {
                                Row contentRow = sheet.createRow(rowCount ++);
                                LineItem lineItem = order.getLineitems().get(0);
                                Payment pmt = null;
                                
                                if(order.isCod())       {
                                        pmt = paymentClient.getPaymentForTxnId(order.getTransactionId()).get(0);
                                } else  {
                                        pmt = paymentClient.getSuccessfulPaymentForTxnId(order.getTransactionId());
                                }
                        
                        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
                        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.getStatusDescription());
                            
                            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());
                }
            } catch (Exception e) {
                        e.printStackTrace();
                }
        }
        
        private void populateOrderSheet(long vendorId, Sheet sheet, CellStyle style, CellStyle rightAlignStyle) {
                
            // Create the title row and put all the titles in it. Rows are 0 based
            Row titleRow = sheet.createRow(rowCount ++);
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellValue("Order Reconciliation Report");
            titleCell.setCellStyle(style);
            
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
            
            sheet.createRow(rowCount ++);
            
            Row headerRow = sheet.createRow(rowCount ++);
            headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
            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.SETTLEMENT_DATE.getValue()).setCellValue("Settlement 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.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");
            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");
            
            sheet.createRow(rowCount ++);
            
                TransactionClient tsc;
        PaymentClient psc;
        LogisticsClient lsc;
        
                try {
                        tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        
                lsc = new LogisticsClient();
                in.shop2020.logistics.LogisticsService.Client logisticsClient = lsc.getClient();
                
                        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());
                }
                
                for (PaymentSettlement paymentSettlement: paymentSettlementsMap.values())       {

                        try {
                                if(paymentSettlement.getPaymentGatewayId() == 4)        {       // If COD
                                        Order order = transactionClient.getOrder(paymentSettlement.getReferenceId());
                                        Transaction txn = transactionClient.getTransaction(order.getTransactionId());
                                        Payment payment = paymentClient.getPaymentForTxnId(txn.getId()).get(0);
                                        
                                        if (order.getVendorId() == vendorId)    {
                                                Row contentRow = sheet.createRow(rowCount ++);
                                                outputCODRow(contentRow, paymentSettlement, order, txn, payment);
                                        }
                                } else  {       // If prepaid
                                        logger.info("ReferenceId: " + paymentSettlement.getReferenceId());
                                        Payment payment = paymentClient.getPayment(paymentSettlement.getReferenceId());
                                        Transaction txn = transactionClient.getTransaction(payment.getMerchantTxnId());
                                        
                                        outputPrepaidTransactionRows(sheet, vendorId, paymentSettlement, txn, payment);
                                }
                        } catch (TransactionServiceException e) {
                                        logger.error("TransactionServiceException " + e);
                                } catch (TException e) {
                                        e.printStackTrace();
                                } catch (PaymentException e) {
                                        logger.error("PaymentException " + e);
                                }
                }
                rowCount ++;
                
                logger.info("Refunded Order Count: " + refundedOrders.size());
                
                for (Order order: refundedOrders)       {
                        try {
                                        Transaction txn = transactionClient.getTransaction(order.getTransactionId());
                                Payment payment = paymentClient.getSuccessfulPaymentForTxnId(txn.getId());
                                PaymentSettlement paymentSettlement = transactionClient.getSettlementForReferenceId(payment.getPaymentId(), payment.getGatewayId(), true);
                                        
                                        outputRefundRows(sheet, vendorId, paymentSettlement, txn, payment);
                        } catch (TransactionServiceException e) {
                                        logger.error("TransactionServiceException " + e);
                                } catch (TException e) {
                                        logger.error("TException " + e);
                                } catch (PaymentException e) {
                                        logger.error("PaymentException " + e);
                                }
                }
//              for (PaymentSettlement paymentSettlement: refundSettlements)    {
//                      try {
//                              Payment payment = paymentClient.getPayment(paymentSettlement.getReferenceId());
//                                      Transaction txn = transactionClient.getTransaction(payment.getMerchantTxnId());
//                                      
//                                      outputRefundRows(sheet, vendorId, paymentSettlement, txn, payment);
//                      } catch (TransactionServiceException e) {
//                                      logger.error("TransactionServiceException " + e);
//                              } catch (TException e) {
//                                      logger.error("TException " + e);
//                              } catch (PaymentException e) {
//                                      logger.error("PaymentException " + e);
//                              }
//              }
                } catch (TTransportException e) {
                        logger.error("TTransportException", e);
                } catch (LogisticsServiceException e) {
                        logger.error("LogisticsServiceException" + e);
                } catch (TException e) {
                        logger.error("TException" + e);
                } catch (PaymentException e) {
                        logger.error("PaymentException" + e);
                }
        }
        
        private void outputRefundRows(Sheet sheet, long vendorId, PaymentSettlement paymentSettlement, Transaction txn, Payment payment)        {
                logger.info("Printing Refunded orders for pmt Id, " + payment.getPaymentId());
                
                boolean isFirstOrderForTransaction = true;
                
                for (Order order: txn.getOrders())      {
                        if(order.getVendorId() == vendorId && refundOrderStatuses.contains(order.getStatus()))  {
                        Row contentRow = sheet.createRow(rowCount ++);
                        LineItem lineItem = order.getLineitems().get(0);
                                
                        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
                        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.getStatusDescription());
                        contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue(paymentGateways.get(payment.getGatewayId()));
                            contentRow.createCell(OrderReportColumn.SETTLEMENT_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(paymentSettlement.getSettlementDate())));
                            contentRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue(txn.getCoupon_code());
                        contentRow.createCell(OrderReportColumn.PAYMENT_STATUS.getValue()).setCellValue(payment.getStatus().name());
                        contentRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue(payment.getPaymentId());
                        
                        if(isFirstOrderForTransaction)  {
                                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());
                                    isFirstOrderForTransaction = false;
                        }
                        }
                }
        }
        
        private void outputPrepaidTransactionRows(Sheet sheet, long vendorId, PaymentSettlement paymentSettlement, Transaction txn, Payment payment)    {
                logger.info("Printing orders for pmt Id, " + payment.getPaymentId());
                
                for (Order order: txn.getOrders())      {
                        if(order.getVendorId() == vendorId && ! order.isVendorPaid() && order.getOriginalOrderId() == 0)        {
                                
                                if(refundOrderStatuses.contains(order.getStatus()))     {
                                        
                                        refundedOrders.add(order);
                                }
                                
                        Row contentRow = sheet.createRow(rowCount ++);
                        LineItem lineItem = order.getLineitems().get(0);
                                
                        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
                        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.getStatusDescription());
                        contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue(paymentGateways.get(payment.getGatewayId()));
                            contentRow.createCell(OrderReportColumn.SETTLEMENT_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(paymentSettlement.getSettlementDate())));
                            contentRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue(txn.getCoupon_code());
                        contentRow.createCell(OrderReportColumn.PAYMENT_STATUS.getValue()).setCellValue(payment.getStatus().name());
                        contentRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue(payment.getPaymentId());
                        
                        contentRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue((paymentSettlement.getServiceTax() * order.getTotal_amount()) / payment.getAmount());
                            contentRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue((paymentSettlement.getOtherCharges() * order.getTotal_amount()) / payment.getAmount());
                            contentRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue((paymentSettlement.getNetCollection() * order.getTotal_amount()) / payment.getAmount());
                        }
                }
        }
        
        private void outputCODRow(Row contentRow, PaymentSettlement paymentSettlement, Order order, Transaction txn, Payment payment)   {
                
                LineItem lineItem = order.getLineitems().get(0);
                
        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
        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.getStatusDescription());
        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.SETTLEMENT_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(paymentSettlement.getSettlementDate())));
            contentRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue(txn.getCoupon_code());
        contentRow.createCell(OrderReportColumn.PAYMENT_STATUS.getValue()).setCellValue(payment.getStatus().name());
        contentRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue(payment.getPaymentId());
        }
        
        private void initiatializeDashboard()   {
                try     {
                        CatalogClient csc = new CatalogClient();
                        Client catalogClient = csc.getClient();
                        OrderStatusGroups orderStatusGroups = new OrderStatusGroups();
                        refundOrderStatuses = orderStatusGroups.getRefundedOrders();
                        
                        vendors = catalogClient.getAllVendors();
                        orderIdsWithoutSuccess = new ArrayList<Long>();
                        
                        TransactionClient tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        
                        ebsSettlementSummaries = transactionClient.getEBSSettlementSummaries();
                        logger.info("", ebsSettlementSummaries);
                        
                } catch (TException e) {
                        logger.error("TException", e);
                        
                } catch (TransactionServiceException e) {
                        logger.error("TransactionServiceException", e);
                }
        }
        
        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 String getReportSource() {
                return reportSource;
        }
        
        public void setReportSource(String reportSource)        {
                this.reportSource = reportSource;
        }
        
    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;
        }
        
        @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;
                this.session = request.getSession();
        }
        
        public Map<Long, String> getEBSSettlementSummaries()    {
                return ebsSettlementSummaries;
        }

        public void setOrderIdsToBeMarkedPaid(File orderIdsToBeMarkedPaid) {
                this.orderIdsToBeMarkedPaid = orderIdsToBeMarkedPaid;
        }

        public File getOrderIdsToBeMarkedPaid() {
                return orderIdsToBeMarkedPaid;
        }
}