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();