Rev 4905 | Rev 5098 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed
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.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;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.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 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.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),GATEWAY_TRANSACTION_ID(16),PAYMENT_TYPE(17),PAYMENT_ID(18),COUPON_CODE(19),SERVICE_TAX(20),OTHER_CHARGES(21),NET_COLLECTION(22),REFUND(23),FINAL_AMOUNT(24),IS_RESHIPED_ORDER(25);// 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 List<Long> orderIdsWithoutSuccess;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 = new ArrayList<OrderStatus>();private List<Order> refundedOrdersBilledinDateRange = new ArrayList<Order>();private List<Order> reshippedOrders = new ArrayList<Order>();private final DateFormat DATE_FORMAT = new SimpleDateFormat("dd/MM/yyyy");public VendorReconciliationController() {try {CatalogClient csc = new CatalogClient();Client catalogClient = csc.getClient();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 String index() {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();}logger.info("Order Ids where no successful payment was found", orderIdsWithoutSuccess);return new DefaultHttpHeaders("report");}public HttpHeaders generateReconciliationReport() {DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");refundOrderStatuses.add(OrderStatus.CANCELLED_ON_CUSTOMER_REQUEST);refundOrderStatuses.add(OrderStatus.RTO_REFUNDED);refundOrderStatuses.add(OrderStatus.DOA_VALID_REFUNDED);refundOrderStatuses.add(OrderStatus.DOA_INVALID_REFUNDED);refundOrderStatuses.add(OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY);refundOrderStatuses.add(OrderStatus.DOA_REFUNDED_RCVD_DAMAGED);refundOrderStatuses.add(OrderStatus.DOA_REFUNDED_LOST_IN_TRANSIT);refundOrderStatuses.add(OrderStatus.RTO_DAMAGED_REFUNDED);refundOrderStatuses.add(OrderStatus.RTO_LOST_IN_TRANSIT_REFUNDED);refundOrderStatuses.add(OrderStatus.RET_PRODUCT_USABLE_REFUNDED);refundOrderStatuses.add(OrderStatus.RET_PRODUCT_UNUSABLE_REFUNDED);refundOrderStatuses.add(OrderStatus.RET_REFUNDED_LOST_IN_TRANSIT);refundOrderStatuses.add(OrderStatus.RET_REFUNDED_RCVD_DAMAGED);refundOrderStatuses.add(OrderStatus.LOST_IN_TRANSIT_REFUNDED);try {//Formatting Form input parametersDate 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);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-from-" + dateFormatForFile.format(startDate) + "-" + dateFormatForFile.format(endDate) + ".xls");ServletOutputStream sos;try {ByteArrayOutputStream baos = getReconciliationReport(vendorId, 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 (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;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.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 = 0;int indexAmount = 1;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;try {if(row.equals(firstRow)) continue;orderId = (long) row.getCell(indexOrderId).getNumericCellValue();double amount = row.getCell(indexAmount).getNumericCellValue();transactionClient.savePaymentSettlements(settlementDate, 4, orderId, 0.0, 0.0, amount);logger.info("SettlementDate: " + settlementDate + ", OrderId: " + orderId + ", Amount: " + amount);countSuccessfulSaves += 1;} 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 HSSFWorkbook(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 indexOrderId = 0;int indexAmount = 1;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 orderId = (long) row.getCell(indexOrderId).getNumericCellValue();double amount = row.getCell(indexAmount).getNumericCellValue();try {transactionClient.savePaymentSettlements(settlementDate, 4, orderId, 0.0, 0.0, amount);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 (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");}// Prepares the XLS worksheet object and fills in the data with proper formattingprivate ByteArrayOutputStream getReconciliationReport(long vendorId, Date startDate, Date endDate) {List<Order> billedOrders = null;List<Order> settledOrders = null;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();billedOrders = transactionClient.getBilledOrdersForVendor(vendorId, startDate.getTime(), endDate.getTime());logger.info("Billed Orders count: " + billedOrders.size());List<PaymentSettlement> paymentSettlements = transactionClient.getSettlementsByDate(startDate.getTime(), endDate.getTime(), false);logger.info("Settlement Count: " + paymentSettlements.size());settledOrders = new ArrayList<Order>();for (PaymentSettlement paymentSettlement: paymentSettlements) {Payment payment = paymentClient.getPayment(paymentSettlement.getReferenceId());Transaction txn = transactionClient.getTransaction(payment.getMerchantTxnId());settledOrders.addAll(txn.getOrders());}logger.info("Settled Orders count: " + settledOrders.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-generatedString errMsg = "Transaction Service Exception occured";logger.error(errMsg, e);addActionError(errMsg);} catch (PaymentException e) {String errMsg = "Transaction Service Exception occured";logger.error(errMsg, e);addActionError(errMsg);}List<Order> settledOrdersForVendor = new ArrayList<Order>();for (Order order: settledOrders) {if(order.getVendorId() == vendorId) {settledOrdersForVendor.add(order);}}logger.info("Settled Order count for Vendor: " + settledOrdersForVendor.size());ByteArrayOutputStream baosXLS = new ByteArrayOutputStream();Workbook wb = new HSSFWorkbook();//Create the style for the title rowFont 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 reshippedOrderSheet = wb.createSheet("Reshipped");Sheet mismatchSheet = wb.createSheet("Mismatches");populateOrderSheet(settledOrdersForVendor, startDate, endDate, orderSheet, boldStyle, rightAlignStyle);populateReshippedOrderSheet(reshippedOrders, reshippedOrderSheet);populateMismatchSheet(mismatchSheet);// Write the workbook to the output streamtry {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("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_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");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;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());}int countMultipleOrderTxns = 0;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("getSuccessfulPaymentForTxnId, " + order.getTransactionId() + ": " + payment);} catch (TApplicationException e) {orderIdsWithoutSuccess.add(order.getId());String errMsg = "Exception while getting successfull payment for transaction Id, " + order.getTransactionId();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.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.SETTLEMENT_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(paymentSettlement.getSettlementDate())));contentRow.createCell(OrderReportColumn.GATEWAY_TRANSACTION_ID.getValue()).setCellValue(payment.getGatewayTxnId());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());contentRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue(serviceTax);contentRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue(otherCollectionCharges);contentRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue(netCollection);}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 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_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");headerRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue("Service Tax");headerRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue("Other Charges");headerRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue("Net Collection");headerRow.createCell(OrderReportColumn.REFUND.getValue()).setCellValue("Refund");headerRow.createCell(OrderReportColumn.FINAL_AMOUNT.getValue()).setCellValue("Final Amount");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());}List <Long> orderIds = new ArrayList<Long>();Map<Long, List<Order>> orderMap = new HashMap<Long, List<Order>>();int countMultipleOrderTxns = 0;for(Order order : orders) {if(order.getBilling_timestamp() == 0) {continue;}orderIds.add(order.getId());if (orderMap.containsKey(order.getTransactionId())) {orderMap.get(order.getTransactionId()).add(order);countMultipleOrderTxns += 1;} else {List<Order> o = new ArrayList<Order>();o.add(order);orderMap.put(order.getTransactionId(), o);}}// Getting list of order ids belonging to reshipped ordersList<Long> reshippedOrderIds = transactionClient.getReshippedOrderIds(orderIds);for(long txnId: orderMap.keySet()) {List<Order> orderList = orderMap.get(txnId);for (Order order: orderList) {logger.info("For order: " + order.getId());// Orders where payment was captured but never billedif(order.getBilling_timestamp() == 0) {continue;}if (reshippedOrderIds.contains(order.getId())) {reshippedOrders.add(order);continue;}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("getSuccessfulPaymentForTxnId, " + order.getTransactionId() + ": " + payment);} catch (TApplicationException e) {orderIdsWithoutSuccess.add(order.getId());String errMsg = "Exception while getting successfull payment for transaction Id, " + order.getTransactionId();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";}double expectedCollection = totalSellingPrice - otherCollectionCharges - serviceTax;//FIXME ignore differences of upto 50 paisaif (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);}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.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.SETTLEMENT_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(paymentSettlement.getSettlementDate())));contentRow.createCell(OrderReportColumn.GATEWAY_TRANSACTION_ID.getValue()).setCellValue(payment.getGatewayTxnId());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());contentRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue(serviceTax);contentRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue(otherCollectionCharges);contentRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue(netCollection);contentRow.createCell(OrderReportColumn.FINAL_AMOUNT.getValue()).setCellValue(netCollection - refund);}}logger.info("Reshipped Orders ", reshippedOrders);logger.info("Order Ids where no successful payment was found: " + orderIdsWithoutSuccess.size());logger.info(orderIdsWithoutSuccess.toString());logger.info("Reshipped Order Ids: " + reshippedOrderIds);logger.info("countMultipleOrderTxns: " + countMultipleOrderTxns);sheet.createRow(serialNo++);List<PaymentSettlement> refundSettlements = transactionClient.getSettlementsByDate(startDate.getTime(), endDate.getTime(), true);logger.info("Refunds: " + refundSettlements.size());logger.info(refundSettlements.toString());Map<Long, PaymentSettlement> refunds = new HashMap<Long, PaymentSettlement>();for (PaymentSettlement settlement: refundSettlements) {refunds.put(settlement.getReferenceId(), settlement);}// Refunds belonging to orders billed before the start date of supplied billing date rangefor (long paymentId: refunds.keySet()) {Payment payment = paymentClient.getPayment(paymentId);Transaction txn = transactionClient.getTransaction(payment.getMerchantTxnId());PaymentSettlement paymentSettlement = refunds.get(paymentId);for (Order order: txn.getOrders()) {if(!refundOrderStatuses.contains(order.getStatus())) continue;Row contentRow = sheet.createRow(serialNo++);LineItem lineItem = order.getLineitems().get(0);double transferPrice = lineItem.getTransfer_price();double totalSellingPrice = lineItem.getTotal_price();String paymentType = paymentGateways.get(payment.getGatewayId()) + "-REFUND";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(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.SETTLEMENT_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(paymentSettlement.getSettlementDate())));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(0.0);contentRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue(0.0);contentRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue(paymentSettlement.getNetCollection());}}} 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);}}public List<Vendor> getAllVendors() {return this.vendors;}private String getValueForEmptyString(String s){if(s==null || s.equals(""))return "-";elsereturn 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;}@Overridepublic void setServletContext(ServletContext context) {this.context = context;}public String getServletContextPath() {return context.getContextPath();}@Overridepublic void setServletResponse(HttpServletResponse response) {this.response = response;}@Overridepublic void setServletRequest(HttpServletRequest request) {this.request = request;this.session = request.getSession();}public Map<Long, String> getEBSSettlementSummaries() {return ebsSettlementSummaries;}}