Subversion Repositories SmartDukaan

package in.shop2020.support.controllers;

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.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.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.PaymentClient;
import in.shop2020.thrift.clients.TransactionClient;

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.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.struts2.convention.annotation.InterceptorRef;
import org.apache.struts2.convention.annotation.InterceptorRefs;
import org.apache.struts2.convention.annotation.Result;
import org.apache.struts2.convention.annotation.Results;
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.TException;
import org.apache.thrift.transport.TTransportException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @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 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),
                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),
                GATEWAY_TRANSACTION_ID(16),
                PAYMENT_TYPE(17),
                PAYMENT_ID(18),
                COUPON_CODE(19),
                SERVICE_TAX(20),
                OTHER_CHARGES(21),
                NET_COLLECTION(22);

//              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 String reportSource;
        
        private File hdfcSettlementReport;
        
        private File ebsSettlementReport;
        private File ebsSettlementSummary;
        
        private String bluedartSettlementDate;
        private File bluedartSettlementReport;
        
        private Map<Long, Map<String, Double>> misMatches = new HashMap<Long, Map<String, Double>>();
        private Map<Long, String> ebsSettlementSummaries;
        
        private final DateFormat DATE_FORMAT = new SimpleDateFormat("dd/MM/yyyy");
        
        public VendorReconciliationController() {
                try     {
                        CatalogClient csc = new CatalogClient();
                        Client catalogClient = csc.getClient();
                        
                        vendors = catalogClient.getAllVendors();

                        TransactionClient tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        
                        ebsSettlementSummaries = transactionClient.getEBSSettlementSummaries();
                        System.out.println(ebsSettlementSummaries);
                        
                } catch (Exception e) {
                        // TODO: handle exception
                }
        }
        
        public String index()   {
                return "report";
        }
        
        public HttpHeaders create()     {
                
                String formType = request.getParameter("formtype");
                System.out.println(formType);
                
                if (formType.equals("uploadEBSSettlementSummary")) {
                        uploadEBSSettlementSummary();
                        
                } else if (formType.equals("uploadEBSSettlements")) {
                        uploadEBSSettlements();
                        
                } else if (formType.equals("uploadHDFCSettlements")) {
                        uploadHDFCSettlements();
                        
                } else if (formType.equals("uploadBluedartSettlements")) {
                        uploadBluedartSettlements();
                }
                return new DefaultHttpHeaders("report");
        }
        
        public HttpHeaders generateReconciliationReport()       {
                DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
                
                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(startDate + " " + endDate + " " + vendorId);
                        
                        CatalogClient csc = new CatalogClient();
                        Client catalogClient= csc.getClient();
                        vendors = catalogClient.getAllVendors();
                        
                        TransactionClient tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        
                        List<Order> orders = transactionClient.getBilledOrdersForVendor(vendorId, startDate.getTime(), endDate.getTime());
                        logger.info("Order count: " + orders.size());
                        DateFormat dateFormatForFile = new SimpleDateFormat("dd.MM.yyyy");
                        String vendorName = getVendorName(vendorId);

                        response.setContentType("application/vnd.ms-excel");
                        response.setHeader("Content-disposition", "inline; filename=" + vendorName + "-reconciliation-from-" + dateFormatForFile.format(startDate) + "-" + dateFormatForFile.format(endDate) + ".xls");
                        
                        ServletOutputStream sos;
                        try {
                                ByteArrayOutputStream baos = getReconciliationReport(orders, startDate, endDate);
                                sos = response.getOutputStream();
                                baos.writeTo(sos);
                                sos.flush();
                        } catch (IOException e) {
                                logger.error("Error while streaming the hotspot reconciliation report", e);
                        }
                } catch (ParseException e)      {
                        logger.error("Unable to parse the start or end date", e);
                } catch (TransactionServiceException e) {
                        logger.error("Error while getting order information from the transaction service", e);
                } catch (TException e) {
            logger.error("Unable to get the orders or the warehouses", e);
                } catch (NullPointerException e)   {
            logger.error("NullPointerException", e);
                } catch (Exception e)   {
            logger.error("Unexpected exception", 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) {
                        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);

                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;
                
                try {
                        tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        
                        for (Row row: sheet)    {
                if(row.equals(firstRow))        continue;
                
                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("'", "");
                        paymentId = Long.parseLong(paymentIdStr);
                        
                        logger.info("paymentId: " + paymentId + ", recfmt: " + recfmt + ", settlementDate: " + settlementDate + ", netCollection: " + netCollection);
                        
                    if (recfmt.equalsIgnoreCase("CVD")) {
                        if (netCollection > 0)  netCollection *= -1;
                        transactionClient.savePaymentSettlements(settlementDate.getTime(), 1, paymentId, 0, 0, netCollection);
                        
                    } 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);
                        }
                } catch (NumberFormatException e) {
                                logger.error("NumberFormatException recieved for payment Id," + paymentIdStr + " "+ e);
                                
                        } catch (TransactionServiceException e) {
                                logger.error("TransactionServiceException recieved for payment Id," + Long.toString(paymentId) + " " + e);
                        }
                        }
                } 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);
                }
        }
        
        public void uploadEBSSettlements()      {
                Workbook wb = null;
        try {
            wb = new HSSFWorkbook(new FileInputStream(this.ebsSettlementReport));
        } 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);
                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;
        
        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);
                }
                }
        } catch (NullPointerException e)        {
                logger.error("" + e);
        }
        
                TransactionClient tsc;
        
        long settlementId = Long.parseLong(request.getParameter("settlementId"));
        
        try     {
                        tsc = new TransactionClient();
                        in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        long settlementDate = transactionClient.getEBSSettlementDate(settlementId);
                        
                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;
                        transactionClient.savePaymentSettlements(settlementDate, 2, paymentId, serviceTax, tdr, amount);
                } else  {
                        double refundedAmount = record.get("Refunded");
                        
                        if (refundedAmount > 0) refundedAmount *= -1;
                        
                        transactionClient.savePaymentSettlements(settlementDate, 2, paymentId, 0.0, 0.0, refundedAmount);
                }
            }
                transactionClient.markEBSSettlementUploaded(settlementId);
                
        } catch (TTransportException e) {
                logger.error("TTransportException " + e);
                } catch (TException e) {
                logger.error("TException " + e);
                } catch (TransactionServiceException e) {
                logger.error("TransactionServiceException " + 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 indexAWBno = 4;             //FIXME: add look up string to determine exact cell
        int indexAmount = 19;
        
        TransactionClient tsc;
        PaymentClient psc;
        
        try     {
                        long settlementDate = DATE_FORMAT.parse(bluedartSettlementDate).getTime();
                tsc = new TransactionClient();
                in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
                        psc = new PaymentClient();
                in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
                
                for (Row row : sheet) {
                        try     {
                                if(row.equals(firstRow))        continue;
                                long awbNumber = (long) row.getCell(indexAWBno).getNumericCellValue();
                                double amount = row.getCell(indexAmount).getNumericCellValue();
//                              mapAWBAndAmount.put(awbNumber, amount);
                                Order order = transactionClient.getOrderForAwb(Long.toString(awbNumber));
                                Payment payment = paymentClient.getSuccessfulPaymentForTxnId(order.getTransactionId());
                                logger.info("settlementDate: " + settlementDate + ", PaymentId: " + payment.getPaymentId() + ", Amount: " + amount);
                                
                                transactionClient.savePaymentSettlements(settlementDate, 4, payment.getPaymentId(), 0.0, 0, amount);
                        } catch (NullPointerException e) {
                                logger.error("" + e);
                                
                        } catch (PaymentException e) {
                                logger.error("" + e);
                                
                                } catch (TransactionServiceException e) {
                                        logger.error("" + e);
                                }
                }
        } catch (ParseException e) {
                logger.error("Could not parse " + bluedartSettlementDate + " " + e);
                } catch (TTransportException e) {
                        logger.error("" + e);
        } catch (TException e) {
                logger.error("" + e);
        }
//        Map<Long, Double> mapAWBAndAmount = new HashMap<Long, Double>();
//        
//        for (Row row : sheet) {
//              try     {
//                if(row.equals(firstRow))      continue;
//                long awbNumber = (long) row.getCell(indexAWBno).getNumericCellValue();
//                double amount = row.getCell(indexAmount).getNumericCellValue();
//                mapAWBAndAmount.put(awbNumber, amount);
//              
//              } catch (NullPointerException e) {
//                      logger.error("" + e);
//              }
//        }
//        
//              TransactionClient tsc;
//              try {
//                      tsc = new TransactionClient();
//                      in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
//                      
//              transactionClient.saveBluedartSettlements(mapAWBAndAmount);
//              } catch (TTransportException e) {
//                      logger.error("" + e);
//              } catch (TransactionServiceException e) {
//                      logger.error("" + e);
//              } catch (TException e) {
//                      logger.error("" + e);
//              }
        }
        
        // Prepares the XLS worksheet object and fills in the data with proper formatting
        private ByteArrayOutputStream getReconciliationReport(List<Order> orders, Date startDate, Date endDate) {
                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("Orders");
                Sheet mismatchSheet = wb.createSheet("Mismatches");
                
            populateOrderSheet(orders, startDate, endDate, orderSheet, boldStyle, rightAlignStyle);
            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 populateOrderSheet(List<Order> orders, Date startDate, Date endDate, Sheet sheet, CellStyle style, CellStyle rightAlignStyle) {
            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("Order Reconciliation Report (" + DATE_FORMAT.format(startDate) + " - " + DATE_FORMAT.format(endDate) + ")");
            titleCell.setCellStyle(style);
            
            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("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.GATEWAY_TRANSACTION_ID.getValue()).setCellValue("Gateway Transaction Id");
            headerRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue("Payment Type");
            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");
            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(serialNo ++);
            
                TransactionClient tsc;
        PaymentClient psc;

                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();
                
                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)       {
                        Row contentRow = sheet.createRow(serialNo++);
                        
                            LineItem lineItem = order.getLineitems().get(0);
                            double transferPrice = lineItem.getTransfer_price();
                            Payment payment = paymentClient.getSuccessfulPaymentForTxnId(order.getTransactionId());
                            
                            Transaction txn = transactionClient.getTransaction(order.getTransactionId());
                            PaymentSettlement paymentSettlement = transactionClient.getSettlementForPaymentId(payment.getPaymentId());
                            
                    double totalSellingPrice = lineItem.getTotal_price();
                    double serviceTax = paymentSettlement.getServiceTax();
                    double otherCollectionCharges = paymentSettlement.getOtherCharges();
                    double netCollection = paymentSettlement.getNetCollection();
                    
                    String paymentType = paymentGateways.get(payment.getGatewayId());
                    
                    if (netCollection < 0.0)    {
                        paymentType = paymentType + "-REFUND";
                    }
                    double expectedCollection = totalSellingPrice - otherCollectionCharges - serviceTax;
                    
                    //FIXME ignore differences of upto 50 paisa
                    if (netCollection > 0.0 && expectedCollection != netCollection)     {
                        Map <String, Double> mismatch = new HashMap<String, Double>();
                        mismatch.put("expected", expectedCollection);
                        mismatch.put("actual", netCollection);
                        misMatches.put(order.getId(), mismatch);
                    }

                    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(DATE_FORMAT.format(new Date(paymentSettlement.getSettlementDate())));
                            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());
                            contentRow.createCell(OrderReportColumn.GATEWAY_TRANSACTION_ID.getValue()).setCellValue(payment.getGatewayTxnId());
                            contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue(paymentType);
                            contentRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue(payment.getPaymentId());
                            contentRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue(txn.getCoupon_code());
                            contentRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue(serviceTax);
                            contentRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue(otherCollectionCharges);
                            contentRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue(netCollection);
                    }
                } catch (TTransportException e) {
                        logger.error("Transport exception", e);
                } catch (PaymentException e) {
                        logger.error("Payment Service exception", e);
                } catch (TException e) {
                        logger.error("Thrift exception", e);
                } catch (TransactionServiceException e) {
                        logger.error("Transaction Service exception", 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;
    }
    
        @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;
        }
}