Subversion Repositories SmartDukaan

Rev

Rev 4715 | Rev 4745 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
4600 varun.gupt 1 
package in.shop2020.support.controllers;
2 
 
3 
import java.io.ByteArrayOutputStream;
4 
import java.io.File;
5 
import java.io.FileInputStream;
6 
import java.io.FileNotFoundException;
7 
import java.io.IOException;
8 
import java.text.DateFormat;
9 
import java.text.ParseException;
10 
import java.text.SimpleDateFormat;
4715 varun.gupt 11 
import java.util.ArrayList;
4600 varun.gupt 12 
import java.util.Date;
13 
import java.util.HashMap;
14 
import java.util.List;
15 
import java.util.Map;
16 
 
4734 varun.gupt 17 
import in.shop2020.logistics.LogisticsServiceException;
18 
import in.shop2020.logistics.Provider;
4600 varun.gupt 19 
import in.shop2020.model.v1.catalog.InventoryService.Client;
20 
import in.shop2020.model.v1.catalog.Vendor;
21 
import in.shop2020.model.v1.order.LineItem;
22 
import in.shop2020.model.v1.order.Order;
4734 varun.gupt 23 
import in.shop2020.model.v1.order.OrderStatus;
4600 varun.gupt 24 
import in.shop2020.model.v1.order.PaymentSettlement;
25 
import in.shop2020.model.v1.order.Transaction;
26 
import in.shop2020.model.v1.order.TransactionServiceException;
27 
import in.shop2020.payments.Payment;
28 
import in.shop2020.payments.PaymentException;
29 
import in.shop2020.payments.PaymentGateway;
30 
import in.shop2020.thrift.clients.CatalogClient;
4734 varun.gupt 31 
import in.shop2020.thrift.clients.LogisticsClient;
4600 varun.gupt 32 
import in.shop2020.thrift.clients.PaymentClient;
33 
import in.shop2020.thrift.clients.TransactionClient;
34 
 
35 
import javax.servlet.ServletContext;
36 
import javax.servlet.ServletOutputStream;
37 
import javax.servlet.http.HttpServletRequest;
38 
import javax.servlet.http.HttpServletResponse;
39 
import javax.servlet.http.HttpSession;
40 
 
41 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
42 
import org.apache.poi.ss.usermodel.Cell;
43 
import org.apache.poi.ss.usermodel.CellStyle;
44 
import org.apache.poi.ss.usermodel.Font;
45 
import org.apache.poi.ss.usermodel.Row;
46 
import org.apache.poi.ss.usermodel.Sheet;
47 
import org.apache.poi.ss.usermodel.Workbook;
48 
import org.apache.poi.ss.util.CellRangeAddress;
4601 varun.gupt 49 
import org.apache.struts2.convention.annotation.InterceptorRef;
50 
import org.apache.struts2.convention.annotation.InterceptorRefs;
51 
import org.apache.struts2.convention.annotation.Result;
52 
import org.apache.struts2.convention.annotation.Results;
4600 varun.gupt 53 
import org.apache.struts2.interceptor.ServletRequestAware;
54 
import org.apache.struts2.interceptor.ServletResponseAware;
55 
import org.apache.struts2.rest.DefaultHttpHeaders;
56 
import org.apache.struts2.rest.HttpHeaders;
57 
import org.apache.struts2.util.ServletContextAware;
4715 varun.gupt 58 
import org.apache.thrift.TApplicationException;
4600 varun.gupt 59 
import org.apache.thrift.TException;
60 
import org.apache.thrift.transport.TTransportException;
61 
import org.slf4j.Logger;
62 
import org.slf4j.LoggerFactory;
63 
 
4715 varun.gupt 64 
import com.opensymphony.xwork2.ValidationAwareSupport;
65 
 
4600 varun.gupt 66 
/**
67 
 * @author Varun Gupta
68 
 * @description: This class handles web requests to generate vendor specific reconciliation reports
69 
 *               for given vendor for a given date range
70 
 */
71 
 
4715 varun.gupt 72 
//@InterceptorRefs({
73 
//    @InterceptorRef("defaultStack"),
74 
//    @InterceptorRef("login")
75 
//})
76 
//@Results({
77 
//    @Result(name="authfail", type="redirectAction", params = {"actionName" , "reports"})
78 
//})
79 
public class VendorReconciliationController extends ValidationAwareSupport implements ServletRequestAware, ServletResponseAware, ServletContextAware {
4600 varun.gupt 80 
 
81 
    private static Logger logger = LoggerFactory.getLogger(VendorReconciliationController.class);
82 
 
83 
	private enum OrderReportColumn {
84 
	    ORDER_ID(0),
85 
	    ORDER_DATE(1),
86 
		BILLING_NUMBER(2),
87 
		BILLING_DATE(3),
88 
		DELIVERY_DATE(4),
89 
		SETTLEMENT_DATE(5),
90 
		CURRENT_STATUS(6),
91 
		BRAND(7),
92 
		MODEL_NAME(8),
93 
		MODEL_NUMBER(9),
94 
		COLOR(10),
95 
		QUANTITY(11),
96 
		UNIT_TRANSFER_PRICE(12),
97 
		UNIT_SELLING_PRICE(13),
98 
		TOTAL_TRANSFER_PRICE(14),
99 
		TOTAL_SELLING_PRICE(15),
100 
		GATEWAY_TRANSACTION_ID(16),
101 
		PAYMENT_TYPE(17),
102 
		PAYMENT_ID(18),
103 
		COUPON_CODE(19),
104 
		SERVICE_TAX(20),
105 
		OTHER_CHARGES(21),
4715 varun.gupt 106 
		NET_COLLECTION(22),
107 
	    REFUND(23),
108 
	    FINAL_AMOUNT(24),
109 
	    IS_RESHIPED_ORDER(25);
4600 varun.gupt 110 
//		COUPON_CATEGORY(19),
111 
//		DISCOUNT(20),
112 
 
113 
		private int value;
114 
 
115 
		OrderReportColumn(int value) {
116 
			this.value = value;
117 
		}
118 
		public int getValue(){
119 
			return this.value;
120 
		}
121 
	}
122 
 
123 
	private enum OrderMismatchColumn	{
124 
		ORDER_ID(0),
125 
		EXPECTED_COLLECTION(1),
126 
		ACTUAL_COLLECTION(2);
127 
 
128 
		private int value;
129 
 
130 
		OrderMismatchColumn(int value) {
131 
			this.value = value;
132 
		}
133 
		public int getValue(){
134 
			return this.value;
135 
		}
136 
	}
137 
 
138 
	private HttpServletRequest request;
139 
	private HttpServletResponse response;
140 
	private ServletContext context;
141 
    private HttpSession session;
142 
	private List<Vendor> vendors;
143 
	private String reportSource;
4715 varun.gupt 144 
	private List<Long> orderIdsWithoutSuccess;
4600 varun.gupt 145 
 
146 
	private File hdfcSettlementReport;
147 
 
148 
	private File ebsSettlementReport;
149 
	private File ebsSettlementSummary;
150 
 
151 
	private String bluedartSettlementDate;
152 
	private File bluedartSettlementReport;
153 
 
4715 varun.gupt 154 
	private String aramexSettlementDate;
155 
	private File aramexSettlementReport;
156 
 
4600 varun.gupt 157 
	private Map<Long, Map<String, Double>> misMatches = new HashMap<Long, Map<String, Double>>();
158 
	private Map<Long, String> ebsSettlementSummaries;
159 
 
4734 varun.gupt 160 
	private List<OrderStatus> refundOrderStatuses = new ArrayList<OrderStatus>();
161 
 
4600 varun.gupt 162 
	private final DateFormat DATE_FORMAT = new SimpleDateFormat("dd/MM/yyyy");
163 
 
164 
	public VendorReconciliationController()	{
165 
		try	{
166 
			CatalogClient csc = new CatalogClient();
167 
			Client catalogClient = csc.getClient();
168 
 
169 
			vendors = catalogClient.getAllVendors();
4715 varun.gupt 170 
			orderIdsWithoutSuccess = new ArrayList<Long>();
171 
 
4600 varun.gupt 172 
			TransactionClient tsc = new TransactionClient();
173 
			in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
174 
 
175 
			ebsSettlementSummaries = transactionClient.getEBSSettlementSummaries();
4715 varun.gupt 176 
			logger.info("", ebsSettlementSummaries);
4600 varun.gupt 177 
 
4715 varun.gupt 178 
		} catch (TException e) {
179 
			logger.error("TException", e);
180 
 
181 
		} catch (TransactionServiceException e) {
182 
			logger.error("TransactionServiceException", e);
4600 varun.gupt 183 
		}
184 
	}
185 
 
186 
	public String index()	{
187 
		return "report";
188 
	}
189 
 
190 
	public HttpHeaders create()	{
191 
 
192 
		String formType = request.getParameter("formtype");
4715 varun.gupt 193 
		logger.info("Form Type: " + formType);
4600 varun.gupt 194 
 
195 
		if (formType.equals("uploadEBSSettlementSummary")) {
196 
			uploadEBSSettlementSummary();
197 
 
198 
		} else if (formType.equals("uploadEBSSettlements")) {
199 
			uploadEBSSettlements();
200 
 
201 
		} else if (formType.equals("uploadHDFCSettlements")) {
202 
			uploadHDFCSettlements();
203 
 
204 
		} else if (formType.equals("uploadBluedartSettlements")) {
205 
			uploadBluedartSettlements();
4715 varun.gupt 206 
 
207 
		} else if (formType.equals("uploadAramexSettlements")) {
208 
			uploadAramexSettlements();
4600 varun.gupt 209 
		}
4715 varun.gupt 210 
	    logger.info("Order Ids where no successful payment was found", orderIdsWithoutSuccess);
211 
 
4600 varun.gupt 212 
		return new DefaultHttpHeaders("report");
213 
	}
214 
 
215 
	public HttpHeaders generateReconciliationReport()	{
216 
		DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
4734 varun.gupt 217 
 
218 
		refundOrderStatuses.add(OrderStatus.CANCELLED_ON_CUSTOMER_REQUEST);
219 
		refundOrderStatuses.add(OrderStatus.RTO_REFUNDED);
220 
		refundOrderStatuses.add(OrderStatus.DOA_VALID_REFUNDED);
221 
		refundOrderStatuses.add(OrderStatus.DOA_INVALID_REFUNDED);
222 
		refundOrderStatuses.add(OrderStatus.CANCELLED_DUE_TO_LOW_INVENTORY);
223 
		refundOrderStatuses.add(OrderStatus.DOA_REFUNDED_RCVD_DAMAGED);
224 
		refundOrderStatuses.add(OrderStatus.DOA_REFUNDED_LOST_IN_TRANSIT);
225 
		refundOrderStatuses.add(OrderStatus.RTO_DAMAGED_REFUNDED);
226 
		refundOrderStatuses.add(OrderStatus.RTO_LOST_IN_TRANSIT_REFUNDED);
227 
		refundOrderStatuses.add(OrderStatus.RET_PRODUCT_USABLE_REFUNDED);
228 
		refundOrderStatuses.add(OrderStatus.RET_PRODUCT_UNUSABLE_REFUNDED);
229 
		refundOrderStatuses.add(OrderStatus.RET_REFUNDED_LOST_IN_TRANSIT);
230 
		refundOrderStatuses.add(OrderStatus.RET_REFUNDED_RCVD_DAMAGED);
231 
		refundOrderStatuses.add(OrderStatus.LOST_IN_TRANSIT_REFUNDED);
4600 varun.gupt 232 
 
233 
		try	{
234 
			//Formatting Form input parameters
235 
 
236 
		    Date startDate = dateFormat.parse(request.getParameter("start"));
237 
			Date endDate = dateFormat.parse(request.getParameter("end"));
238 
		    long vendorId = Long.parseLong(request.getParameter("vendor"));
239 
 
240 
		    logger.info(startDate + " " + endDate + " " + vendorId);
241 
 
242 
			TransactionClient tsc = new TransactionClient();
243 
			in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
244 
 
245 
			List<Order> orders = transactionClient.getBilledOrdersForVendor(vendorId, startDate.getTime(), endDate.getTime());
246 
			logger.info("Order count: " + orders.size());
247 
			DateFormat dateFormatForFile = new SimpleDateFormat("dd.MM.yyyy");
248 
			String vendorName = getVendorName(vendorId);
4715 varun.gupt 249 
 
4600 varun.gupt 250 
			response.setContentType("application/vnd.ms-excel");
251 
			response.setHeader("Content-disposition", "inline; filename=" + vendorName + "-reconciliation-from-" + dateFormatForFile.format(startDate) + "-" + dateFormatForFile.format(endDate) + ".xls");
252 
 
253 
			ServletOutputStream sos;
254 
			try {
255 
				ByteArrayOutputStream baos = getReconciliationReport(orders, startDate, endDate);
256 
				sos = response.getOutputStream();
257 
				baos.writeTo(sos);
258 
				sos.flush();
259 
			} catch (IOException e)	{
260 
				logger.error("Error while streaming the hotspot reconciliation report", e);
261 
			}
262 
		} catch (ParseException e)	{
263 
			logger.error("Unable to parse the start or end date", e);
264 
		} catch (TransactionServiceException e)	{
265 
			logger.error("Error while getting order information from the transaction service", e);
266 
		} catch (TException e) {
267 
            logger.error("Unable to get the orders or the warehouses", e);
268 
		} catch (NullPointerException e)   {
269 
            logger.error("NullPointerException", e);
270 
		} catch (Exception e)   {
271 
            logger.error("Unexpected exception", e);
272 
        }
273 
		return new DefaultHttpHeaders("report");
274 
	}
275 
 
276 
	public void uploadEBSSettlementSummary()	{
277 
 
278 
		DateFormat settlementDateFormat = new SimpleDateFormat("dd MMM,yyyy hh:mm:ss a");
279 
		DateFormat transactionDateFormat = new SimpleDateFormat("yyyy-MM-dd");
280 
 
281 
		Workbook wb = null;
282 
        try {
283 
            wb = new HSSFWorkbook(new FileInputStream(ebsSettlementSummary));
284 
        } catch (FileNotFoundException e) {
285 
            logger.error("Unable to open the Settlement Summary report", e);
286 
        } catch (IOException e) {
287 
            logger.error("Unable to open the Settlement Summary report", e);
288 
        }
289 
        Sheet sheet = wb.getSheetAt(0);
290 
 
291 
        int indexSettlementId = 0;
292 
        int indexSettlementDate = 1;
293 
        int indexTransactionDateFrom = 2;
294 
        int indexTransactionDateTo = 3;
295 
        int indexAmount = 4;
296 
 
297 
        Row firstRow = sheet.getRow(0);
298 
        Row secondRow = sheet.getRow(1);
299 
        Row thirdRow = sheet.getRow(2);
300 
        Row fourthRow = sheet.getRow(3);
301 
        Row fifthRow = sheet.getRow(4);
302 
 
303 
		TransactionClient tsc;
304 
 
305 
		try {
306 
			tsc = new TransactionClient();
307 
			in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
308 
 
309 
			for(Row row: sheet)	{
310 
				if(row.equals(firstRow) || row.equals(secondRow) || row.equals(thirdRow) || row.equals(fourthRow) || row.equals(fifthRow))	{
311 
					continue;
312 
				}
313 
				long settlementId = (long) row.getCell(indexSettlementId).getNumericCellValue();
314 
				String settlementDateStr = row.getCell(indexSettlementDate).getStringCellValue();
315 
				String transactionDateFromStr = row.getCell(indexTransactionDateFrom).getStringCellValue();
316 
				String transactionDateToStr = row.getCell(indexTransactionDateTo).getStringCellValue();
317 
				Double amount = row.getCell(indexAmount).getNumericCellValue();
318 
 
319 
				Date settlementDate = settlementDateFormat.parse(settlementDateStr);
320 
				Date transactionDateFrom = transactionDateFormat.parse(transactionDateFromStr);
321 
				Date transactionDateTo = transactionDateFormat.parse(transactionDateToStr);
322 
 
323 
				try	{
324 
					transactionClient.saveEBSSettlementSummary(settlementId, settlementDate.getTime(), transactionDateFrom.getTime(), transactionDateTo.getTime(), amount);
325 
 
326 
				} catch (Exception e) {
327 
					e.printStackTrace();
328 
				}
329 
			}
330 
        } catch (Exception e) {
331 
        	e.printStackTrace();
332 
		}
333 
	}
334 
 
335 
	public void uploadHDFCSettlements()	{
336 
		logger.info("Uploading HDFC settlements");
337 
 
338 
		Workbook wb = null;
339 
		try {
340 
			wb = new HSSFWorkbook(new FileInputStream(this.hdfcSettlementReport));
341 
		} catch (FileNotFoundException e) {
4734 varun.gupt 342 
			String errMsg = "Unable to open the HDFC Payout report";
343 
			logger.error(errMsg, e);
344 
			addActionError(errMsg);
345 
			return;
4600 varun.gupt 346 
		} catch (IOException e) {
4734 varun.gupt 347 
			String errMsg = "Unable to open the HDFC Payout report";
348 
			logger.error(errMsg, e);
349 
			addActionError(errMsg);
350 
			return;
4600 varun.gupt 351 
		}
352 
		Sheet sheet = wb.getSheetAt(0);
353 
 
354 
		Row firstRow = sheet.getRow(0);
355 
 
356 
		int indexRECFMT = 2;
357 
		int indexSettlementDate = 7;
358 
		int indexPaymentId = 13;
359 
		int indexMSF = 14;
360 
		int indexServiceTax = 15;
361 
		int indexEduCess = 16;
362 
		int indexNetCollection = 17;
363 
 
364 
		TransactionClient tsc;
365 
		String paymentIdStr = "";
366 
		long paymentId = 0;
4734 varun.gupt 367 
 
368 
		int countSuccessfulInserts = 0;
369 
		int countRefunds = 0;
4600 varun.gupt 370 
 
371 
		try {
372 
			tsc = new TransactionClient();
373 
			in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
374 
 
375 
			for (Row row: sheet)	{
376 
                if(row.equals(firstRow))	continue;
377 
 
378 
                try	{
379 
                    String recfmt = row.getCell(indexRECFMT).getStringCellValue();
380 
                    Date settlementDate = row.getCell(indexSettlementDate).getDateCellValue();
381 
                	double netCollection = row.getCell(indexNetCollection).getNumericCellValue();
382 
                	paymentIdStr = row.getCell(indexPaymentId).getStringCellValue().replace("'", "");
383 
                	paymentId = Long.parseLong(paymentIdStr);
384 
 
385 
                	logger.info("paymentId: " + paymentId + ", recfmt: " + recfmt + ", settlementDate: " + settlementDate + ", netCollection: " + netCollection);
386 
 
4715 varun.gupt 387 
                    if (recfmt.trim().equalsIgnoreCase("CVD"))	{
388 
                    	if (netCollection > 0.0)	netCollection *= -1.0;
389 
                    	transactionClient.savePaymentSettlements(settlementDate.getTime(), 1, paymentId, 0.0, 0.0, netCollection);
4734 varun.gupt 390 
                    	countRefunds += 1;
4600 varun.gupt 391 
 
392 
                    } else	{
393 
                    	double msf = row.getCell(indexMSF).getNumericCellValue();
394 
                    	double serviceTax = row.getCell(indexServiceTax).getNumericCellValue();
395 
                    	double eduCess = row.getCell(indexEduCess).getNumericCellValue();
396 
                    	logger.info("msf: " + msf + ", serviceTax: " + serviceTax + ", eduCess: " + eduCess);
397 
 
398 
                    	transactionClient.savePaymentSettlements(settlementDate.getTime(), 1, paymentId, serviceTax, (eduCess + msf), netCollection);
4734 varun.gupt 399 
                    	countSuccessfulInserts += 1;
4600 varun.gupt 400 
                 	}
401 
                } catch (NumberFormatException e) {
4734 varun.gupt 402 
                	String errMsg = "NumberFormatException recieved for payment Id, " + paymentIdStr;
403 
        			logger.error(errMsg + e);
404 
        			addActionError(errMsg);
4600 varun.gupt 405 
 
406 
        		} catch (TransactionServiceException e) {
4734 varun.gupt 407 
                	String errMsg = "TransactionServiceException recieved for payment Id, " + paymentId;
408 
        			logger.error(errMsg + e);
409 
        			addActionError(errMsg);
4600 varun.gupt 410 
        		}
411 
			}
412 
		} catch (TTransportException e) {
413 
			logger.error("TTransportException recieved for payment Id," + Long.toString(paymentId) + " " + e);
414 
 
415 
		} catch (TException e) {
416 
			logger.error("TException recieved for payment Id," + Long.toString(paymentId) + " " + e);
417 
		}
4734 varun.gupt 418 
		addActionMessage("Added settlements for " + countSuccessfulInserts + " payments");
419 
		addActionMessage("Added settlements for " + countRefunds + " refund payments");
4600 varun.gupt 420 
	}
421 
 
422 
	public void uploadEBSSettlements()	{
423 
		Workbook wb = null;
424 
        try {
425 
            wb = new HSSFWorkbook(new FileInputStream(this.ebsSettlementReport));
426 
        } catch (FileNotFoundException e) {
4734 varun.gupt 427 
            logger.error("Unable to open the EBS Settlement detail report", e);
4600 varun.gupt 428 
        } catch (IOException e) {
4734 varun.gupt 429 
            logger.error("Unable to open the EBS Settlement detail report", e);
4600 varun.gupt 430 
        }
431 
 
432 
        Sheet sheet = wb.getSheetAt(0);
433 
 
434 
		Row firstRow = sheet.getRow(0);
435 
		Row secondRow = sheet.getRow(1);
436 
 
437 
        int indexPaymentId = 2;
438 
        int indexTxnType = 5;
439 
        int indexAmount = 6;
440 
 
441 
        Map<Long, Map<String, Double>> map = new HashMap<Long, Map<String,Double>>();
442 
        Map <String, Double> record;
4734 varun.gupt 443 
        int countPaymentIds = 0;
4600 varun.gupt 444 
 
445 
        try	{
446 
        	for (Row row: sheet)	{
447 
                if(row.equals(firstRow) || row.equals(secondRow))	continue;
448 
 
449 
                long paymentId = Long.parseLong(row.getCell(indexPaymentId).getStringCellValue());
450 
                String transactionType = row.getCell(indexTxnType).getStringCellValue();
451 
                double amount = row.getCell(indexAmount).getNumericCellValue();
452 
 
453 
                if(map.containsKey(paymentId))	{
454 
                	map.get(paymentId).put(transactionType, amount);
455 
 
456 
                } else	{
457 
                	record = new HashMap<String, Double>();
458 
                	record.put(transactionType, amount);
459 
                	map.put(paymentId, record);
4734 varun.gupt 460 
                	countPaymentIds += 1;
4600 varun.gupt 461 
                }
462 
        	}
463 
        } catch	(NullPointerException e)	{
464 
        	logger.error("" + e);
465 
        }
4734 varun.gupt 466 
        addActionMessage("Total Payment Ids added: " + countPaymentIds);
4600 varun.gupt 467 
 
468 
		TransactionClient tsc;
469 
        long settlementId = Long.parseLong(request.getParameter("settlementId"));
470 
 
4734 varun.gupt 471 
        int countPaymentsRecieved = 0;
472 
        int countPaymentsRefunded = 0;
473 
 
4600 varun.gupt 474 
        try	{
475 
			tsc = new TransactionClient();
476 
			in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
4734 varun.gupt 477 
			long settlementDate;
4600 varun.gupt 478 
 
4734 varun.gupt 479 
			try	{
480 
				settlementDate = transactionClient.getEBSSettlementDate(settlementId);
481 
			} catch (TransactionServiceException e) {
482 
				String errMsg = "Could not retrieve EBS Settlement date for settlementId, " + settlementId;
483 
	        	logger.error(errMsg + e);
484 
	        	addActionError(errMsg);
485 
	        	return;
486 
			}
487 
 
4600 varun.gupt 488 
        	for (long paymentId: map.keySet())	{
489 
            	record = map.get(paymentId);
490 
 
491 
            	if(record.containsKey("Captured"))	{
492 
            		double capturedAmount = record.get("Captured");
493 
            		double tdr = record.get("TDR");
494 
            		double serviceTax = record.get("ServiceTax");
495 
 
496 
            		if(tdr < 0)	tdr *= -1;
497 
            		if(serviceTax < 0)	serviceTax *= -1;
498 
 
499 
            		double amount = capturedAmount - tdr - serviceTax;
4734 varun.gupt 500 
 
501 
            		try	{
502 
            			transactionClient.savePaymentSettlements(settlementDate, 2, paymentId, serviceTax, tdr, amount);
503 
        			} catch (TransactionServiceException e) {
504 
        				String errMsg = "Could not save settlement for paymentId, " + paymentId;
505 
        	        	logger.error(errMsg + e);
506 
        	        	addActionError(errMsg);
507 
        			}
508 
            		countPaymentsRecieved += 1;
509 
            	}
510 
 
511 
            	if(record.containsKey("Refunded"))	{
4600 varun.gupt 512 
            		double refundedAmount = record.get("Refunded");
513 
 
514 
            		if (refundedAmount > 0)	refundedAmount *= -1;
515 
 
4734 varun.gupt 516 
            		try	{
517 
            			transactionClient.savePaymentSettlements(settlementDate, 2, paymentId, 0.0, 0.0, refundedAmount);
518 
        			} catch (TransactionServiceException e) {
519 
        				String errMsg = "Could not save refund settlement for paymentId, " + paymentId;
520 
        	        	logger.error(errMsg + e);
521 
        	        	addActionError(errMsg);
522 
        			}
523 
            		countPaymentsRefunded += 1;
4600 varun.gupt 524 
            	}
525 
            }
4734 varun.gupt 526 
        	addActionMessage("Total count of payments recieved: " + countPaymentsRecieved);
527 
        	addActionMessage("Total count of payments refunded: " + countPaymentsRefunded);
4600 varun.gupt 528 
 
4734 varun.gupt 529 
        	try	{
530 
        		transactionClient.markEBSSettlementUploaded(settlementId);
531 
			} catch (TransactionServiceException e) {
532 
				String errMsg = "Could not mark settlement for details Uploaded. Settlement ID: " + settlementId;
533 
	        	logger.error(errMsg + e);
534 
	        	addActionError(errMsg);
535 
			}
4600 varun.gupt 536 
        } catch (TTransportException e) {
537 
        	logger.error("TTransportException " + e);
538 
		} catch (TException e) {
539 
        	logger.error("TException " + e);
540 
		}
541 
	}
542 
 
543 
	public void uploadBluedartSettlements()	{
544 
 
545 
		Workbook wb = null;
546 
        try {
547 
            wb = new HSSFWorkbook(new FileInputStream(this.bluedartSettlementReport));
548 
        } catch (FileNotFoundException e) {
549 
            logger.error("Unable to open the Settlement report", e);
550 
        } catch (IOException e) {
551 
            logger.error("Unable to open the Settlement report", e);
552 
        }
553 
 
554 
        Sheet sheet = wb.getSheetAt(0);
555 
 
556 
        Row firstRow = sheet.getRow(0);
557 
        logger.info("Cell count:" + firstRow.getPhysicalNumberOfCells());
558 
 
4734 varun.gupt 559 
        int indexOrderId = 0;
560 
        int indexAmount = 1;
4600 varun.gupt 561 
 
562 
        TransactionClient tsc;
563 
        PaymentClient psc;
4734 varun.gupt 564 
    	int countSuccessfulSaves = 0;
4600 varun.gupt 565 
 
566 
        try	{
567 
			long settlementDate = DATE_FORMAT.parse(bluedartSettlementDate).getTime();
568 
        	tsc = new TransactionClient();
569 
        	in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
570 
			psc = new PaymentClient();
571 
	    	in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
572 
 
573 
        	for (Row row : sheet) {
4715 varun.gupt 574 
        		Order order = null;
4600 varun.gupt 575 
        		try	{
576 
        			if(row.equals(firstRow))	continue;
4734 varun.gupt 577 
        			long orderId = (long) row.getCell(indexOrderId).getNumericCellValue();
4715 varun.gupt 578 
 
4600 varun.gupt 579 
        			double amount = row.getCell(indexAmount).getNumericCellValue();
4734 varun.gupt 580 
        			order = transactionClient.getOrder(orderId);
581 
        			Payment payment = paymentClient.getPaymentForTxnId(order.getTransactionId()).get(0);
4600 varun.gupt 582 
        			logger.info("settlementDate: " + settlementDate + ", PaymentId: " + payment.getPaymentId() + ", Amount: " + amount);
583 
 
584 
        			transactionClient.savePaymentSettlements(settlementDate, 4, payment.getPaymentId(), 0.0, 0, amount);
4734 varun.gupt 585 
        			countSuccessfulSaves += 1;
4600 varun.gupt 586 
        		} catch (NullPointerException e) {
587 
        			logger.error("" + e);
588 
 
589 
        		} catch (PaymentException e) {
590 
        			logger.error("" + e);
591 
 
592 
				} catch (TransactionServiceException e) {
593 
					logger.error("" + e);
4715 varun.gupt 594 
 
595 
				} catch (TApplicationException e)	{
596 
					orderIdsWithoutSuccess.add(order.getId());
597 
 
598 
					logger.error("Application Exception while getting successfull payment for transaction Id, " + order.getTransactionId(), e);
599 
					addActionError("Application Exception while getting payment for transaction Id, " + order.getTransactionId());
4600 varun.gupt 600 
				}
601 
        	}
602 
        } catch (ParseException e) {
603 
        	logger.error("Could not parse " + bluedartSettlementDate + " " + e);
604 
		} catch (TTransportException e) {
605 
			logger.error("" + e);
606 
        } catch (TException e) {
607 
        	logger.error("" + e);
608 
        }
4734 varun.gupt 609 
        addActionMessage("Successfully added Bluedart settlement details for " + countSuccessfulSaves + " orders");
4600 varun.gupt 610 
	}
611 
 
4715 varun.gupt 612 
	public void uploadAramexSettlements()	{
613 
		logger.info("Uploading Aramex Settlements" + this.aramexSettlementDate);
614 
 
615 
		if(this.aramexSettlementDate.equals(""))	{
616 
			addActionError("Settlement date cannot be left blank");
617 
			return;
618 
		}
619 
 
620 
		Workbook wb = null;
621 
        try {
622 
            wb = new HSSFWorkbook(new FileInputStream(this.aramexSettlementReport));
623 
        } catch (FileNotFoundException e) {
624 
            logger.error("Unable to open the Settlement report", e);
625 
        } catch (IOException e) {
626 
            logger.error("Unable to open the Settlement report", e);
627 
        } catch (NullPointerException e) {
628 
        	addActionError(e.getMessage());
629 
        	logger.error("Unable to open the Settlement report", e);
630 
        	return;
631 
		}
632 
 
633 
        Sheet sheet = wb.getSheetAt(0);
634 
 
635 
        int indexOrderId = 0;
4734 varun.gupt 636 
        int indexAmount = 1;
4715 varun.gupt 637 
 
638 
        Row firstRow = sheet.getRow(0);
639 
        logger.info("Cell count:" + firstRow.getPhysicalNumberOfCells());
640 
 
641 
        TransactionClient tsc;
642 
        PaymentClient psc;
4734 varun.gupt 643 
        int countSuccessfulSaves = 0;
4715 varun.gupt 644 
        try	{
645 
			long settlementDate = DATE_FORMAT.parse(aramexSettlementDate).getTime();
646 
 
647 
        	tsc = new TransactionClient();
648 
        	in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
649 
			psc = new PaymentClient();
650 
	    	in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
651 
 
652 
	    	for (Row row: sheet)	{
653 
	    		if(row.equals(firstRow))	continue;
654 
 
655 
	    		long orderId = (long) row.getCell(indexOrderId).getNumericCellValue();
656 
	    		double amount = row.getCell(indexAmount).getNumericCellValue();
657 
	    		Order order = null;
658 
 
659 
	    		try {
660 
					order = transactionClient.getOrder(orderId);
4734 varun.gupt 661 
					Payment payment = paymentClient.getPaymentForTxnId(order.getTransactionId()).get(0);
4715 varun.gupt 662 
					transactionClient.savePaymentSettlements(settlementDate, 4, payment.getPaymentId(), 0.0, 0.0, amount);
4734 varun.gupt 663 
					countSuccessfulSaves += 1;
4715 varun.gupt 664 
 
665 
				} catch (TransactionServiceException e) {
666 
					logger.error("Transaction Service Exception while getting order for id, " + orderId, e);
667 
					addActionError("Transaction Service Exception while getting order for id, " + orderId);
668 
 
669 
				} catch (PaymentException e) {
670 
					logger.error("Payment Service Exception while getting successfull payment for transaction Id, " + order.getTransactionId(), e);
671 
					addActionError("Payment Service Exception while getting payment for transaction Id, " + order.getTransactionId());
672 
 
673 
				} catch (TApplicationException e)	{
674 
					orderIdsWithoutSuccess.add(orderId);
675 
					logger.error("Application Exception while getting successfull payment for transaction Id, " + order.getTransactionId(), e);
676 
					addActionError("Application Exception while getting payment for transaction Id, " + order.getTransactionId());
677 
				}
678 
	    	}
679 
        } catch (ParseException e) {
680 
        	addActionError("Could not parse " + aramexSettlementDate);
681 
        	logger.error("Could not parse " + aramexSettlementDate + " " + e);
682 
        	return;
683 
        } catch (TTransportException e) {
684 
        	logger.error("" + e);
685 
        } catch (TException e) {
686 
        	logger.error("" + e);
687 
        }
4734 varun.gupt 688 
        addActionMessage("Successfully added Aramex settlement details for " + countSuccessfulSaves + " orders");
4715 varun.gupt 689 
	}
690 
 
4600 varun.gupt 691 
	// Prepares the XLS worksheet object and fills in the data with proper formatting
692 
	private ByteArrayOutputStream getReconciliationReport(List<Order> orders, Date startDate, Date endDate)	{
693 
		ByteArrayOutputStream baosXLS = new ByteArrayOutputStream();
694 
 
695 
	    Workbook wb = new HSSFWorkbook();
696 
 
697 
	    //Create the style for the title row
698 
	    Font font = wb.createFont();
699 
	    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
700 
	    CellStyle boldStyle = wb.createCellStyle();
701 
	    boldStyle.setFont(font);
702 
 
703 
	    CellStyle rightAlignStyle = wb.createCellStyle();
704 
	    rightAlignStyle.setAlignment(CellStyle.ALIGN_RIGHT);
705 
 
706 
		Sheet orderSheet = wb.createSheet("Orders");
707 
		Sheet mismatchSheet = wb.createSheet("Mismatches");
708 
 
709 
	    populateOrderSheet(orders, startDate, endDate, orderSheet, boldStyle, rightAlignStyle);
710 
	    populateMismatchSheet(mismatchSheet);
711 
 
712 
		// Write the workbook to the output stream
713 
		try {
714 
			wb.write(baosXLS);
715 
			baosXLS.close();
716 
		} catch (IOException e) {
717 
			logger.error("Unable to write the hotspot reconciliation report to the byte array", e);
718 
		}
719 
		return baosXLS;
720 
	}
721 
 
722 
	private void populateMismatchSheet(Sheet sheet)	{
723 
	    int serialNo = 0;
724 
		Row titleRow = sheet.createRow(serialNo ++);
725 
	    Cell titleCell = titleRow.createCell(0);
726 
	    titleCell.setCellValue("Order Reconciliation Report (Mis-matches)");
727 
 
728 
	    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
729 
	    sheet.createRow(serialNo ++);
730 
 
731 
	    Row headerRow = sheet.createRow(serialNo ++);
732 
	    headerRow.createCell(OrderMismatchColumn.ORDER_ID.getValue()).setCellValue("Order Id");
733 
	    headerRow.createCell(OrderMismatchColumn.EXPECTED_COLLECTION.getValue()).setCellValue("Expected Collection");
734 
	    headerRow.createCell(OrderMismatchColumn.ACTUAL_COLLECTION.getValue()).setCellValue("Actual Collection");
735 
 
736 
	    for (long orderId: misMatches.keySet())	{
737 
	    	Row contentRow = sheet.createRow(serialNo ++);
738 
	    	contentRow.createCell(OrderMismatchColumn.ORDER_ID.getValue()).setCellValue(orderId);
739 
	    	contentRow.createCell(OrderMismatchColumn.EXPECTED_COLLECTION.getValue()).setCellValue(misMatches.get(orderId).get("expected"));
740 
	    	contentRow.createCell(OrderMismatchColumn.ACTUAL_COLLECTION.getValue()).setCellValue(misMatches.get(orderId).get("actual"));
741 
	    }
742 
	}
743 
 
744 
	private void populateOrderSheet(List<Order> orders, Date startDate, Date endDate, Sheet sheet, CellStyle style, CellStyle rightAlignStyle) {
745 
	    short serialNo = 0;
746 
 
747 
	    // Create the title row and put all the titles in it. Rows are 0 based.
748 
	    Row titleRow = sheet.createRow(serialNo ++);
749 
	    Cell titleCell = titleRow.createCell(0);
750 
	    titleCell.setCellValue("Order Reconciliation Report (" + DATE_FORMAT.format(startDate) + " - " + DATE_FORMAT.format(endDate) + ")");
751 
	    titleCell.setCellStyle(style);
752 
 
753 
	    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
754 
 
755 
	    sheet.createRow(serialNo ++);
756 
 
757 
	    Row headerRow = sheet.createRow(serialNo ++);
758 
	    headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
759 
	    headerRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue("Order Date");
760 
	    headerRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue("Billing Number");
761 
	    headerRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue("Billing Date");
762 
	    headerRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue("Delivery Date");
763 
	    headerRow.createCell(OrderReportColumn.SETTLEMENT_DATE.getValue()).setCellValue("Settlement Date");
764 
	    headerRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue("Brand");
765 
	    headerRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue("Model Name");
766 
	    headerRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue("Model Number");
767 
	    headerRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue("Color");
768 
	    headerRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue("Quantity");
769 
	    headerRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue("Unit Transfer Price");
770 
	    headerRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue("Unit Selling Price");
771 
	    headerRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue("Total Transfer Price");
772 
	    headerRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue("Total Selling Price");
773 
	    headerRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue("Current Status");
774 
	    headerRow.createCell(OrderReportColumn.GATEWAY_TRANSACTION_ID.getValue()).setCellValue("Gateway Transaction Id");
775 
	    headerRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue("Payment Type");
776 
	    headerRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue("Payment Id");
777 
	    headerRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue("Coupon Code");
778 
//	    headerRow.createCell(OrderReportColumn.COUPON_CATEGORY.getValue()).setCellValue("Coupon Category");
779 
//	    headerRow.createCell(OrderReportColumn.DISCOUNT.getValue()).setCellValue("Discount");
780 
	    headerRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue("Service Tax");
781 
	    headerRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue("Other Charges");
782 
	    headerRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue("Net Collection");
4715 varun.gupt 783 
	    headerRow.createCell(OrderReportColumn.REFUND.getValue()).setCellValue("Refund");
784 
	    headerRow.createCell(OrderReportColumn.FINAL_AMOUNT.getValue()).setCellValue("Final Amount");
785 
	    headerRow.createCell(OrderReportColumn.IS_RESHIPED_ORDER.getValue()).setCellValue("Is Reshipped Order");
4600 varun.gupt 786 
 
787 
	    sheet.createRow(serialNo ++);
788 
 
789 
		TransactionClient tsc;
790 
    	PaymentClient psc;
4734 varun.gupt 791 
    	LogisticsClient lsc;
4600 varun.gupt 792 
 
793 
		try {
794 
			tsc = new TransactionClient();
795 
			in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
796 
 
797 
			psc = new PaymentClient();
798 
	    	in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
799 
 
4734 varun.gupt 800 
	    	lsc = new LogisticsClient();
801 
	    	in.shop2020.logistics.LogisticsService.Client logisticsClient = lsc.getClient();
802 
 
803 
	    	Map<Long, String> codProviders = new HashMap<Long, String>();
804 
 
805 
	    	for (Provider provider: logisticsClient.getAllProviders())	{
806 
	    		codProviders.put(provider.getId(), provider.getName());
807 
	    	}
808 
 
4600 varun.gupt 809 
	    	List<PaymentGateway> tPaymentGateways = paymentClient.getActivePaymentGateways();
810 
	    	Map<Long, String> paymentGateways = new HashMap<Long, String>();
811 
 
812 
	    	for (PaymentGateway tPaymentGateway: tPaymentGateways)	{
813 
	    		paymentGateways.put(tPaymentGateway.getId(), tPaymentGateway.getName());
814 
	    	}
815 
 
4715 varun.gupt 816 
			List<PaymentSettlement> settlements = transactionClient.getSettlementsByDate(startDate.getTime(), endDate.getTime(), true);
817 
			logger.info("Refunds: " + settlements.size());
818 
			logger.info(settlements.toString());
819 
 
820 
			Map<Long, PaymentSettlement> refunds = new HashMap<Long, PaymentSettlement>();
821 
 
822 
			for (PaymentSettlement settlement: settlements)	{
823 
				refunds.put(settlement.getPaymentId(), settlement);
824 
			}
825 
 
826 
			List <Long> orderIds = new ArrayList<Long>();
4734 varun.gupt 827 
			Map<Long, List<Order>> orderMap = new HashMap<Long, List<Order>>();
4715 varun.gupt 828 
 
4600 varun.gupt 829 
			for(Order order : orders)	{
4715 varun.gupt 830 
				orderIds.add(order.getId());
4734 varun.gupt 831 
 
832 
				if (orderMap.containsKey(order.getTransactionId()))	{
833 
					orderMap.get(order.getTransactionId()).add(order);
834 
 
835 
				} else	{
836 
					List<Order> o = new ArrayList<Order>();
837 
					o.add(order);
838 
					orderMap.put(order.getTransactionId(), o);
839 
				}
4715 varun.gupt 840 
			}
841 
 
842 
			List<Long> reshippedOrderIds = null;
843 
 
844 
			reshippedOrderIds = transactionClient.getReshippedOrderIds(orderIds);
845 
 
4734 varun.gupt 846 
			for(long txnId: orderMap.keySet())	{
847 
				List<Order> orderList = orderMap.get(txnId);
848 
 
849 
				for (Order order: orderList)	{
850 
				    logger.info("For order: " + order.getId());
851 
 
852 
				    LineItem lineItem = order.getLineitems().get(0);
853 
				    double transferPrice = lineItem.getTransfer_price();
854 
				    Payment payment = null;
4715 varun.gupt 855 
 
4734 varun.gupt 856 
				    try	{
857 
				    	payment = paymentClient.getSuccessfulPaymentForTxnId(order.getTransactionId());
858 
					    logger.info("getSuccessfulPaymentForTxnId: " + payment);
859 
 
860 
				    } catch (TApplicationException e)	{
861 
				    	orderIdsWithoutSuccess.add(order.getId());
862 
 
863 
						logger.error("Exception while getting successfull payment for transaction Id, " + order.getTransactionId(), e);
864 
						addActionError("Exception while getting payment for transaction Id, " + order.getTransactionId());
865 
					}
866 
 
867 
				    Transaction txn = transactionClient.getTransaction(order.getTransactionId());
868 
				    PaymentSettlement paymentSettlement = null;
869 
				    String paymentType = null;
870 
 
871 
				    if(payment != null)	{
872 
				    	paymentSettlement = transactionClient.getSettlementForPaymentId(payment.getPaymentId());
873 
				    	paymentType = paymentGateways.get(payment.getGatewayId());
874 
				    }
875 
 
876 
		            double totalSellingPrice = lineItem.getTotal_price();
877 
		            double serviceTax = 0.0;
878 
		            double otherCollectionCharges = 0.0;
879 
		            double netCollection = 0.0;
880 
 
881 
		            if(paymentSettlement != null)	{
882 
			            serviceTax = paymentSettlement.getServiceTax();
883 
			            otherCollectionCharges = paymentSettlement.getOtherCharges();
884 
			            netCollection = paymentSettlement.getNetCollection();
885 
		            }
886 
 
887 
		            if (netCollection < 0.0)	{
888 
		            	paymentType = paymentType + "-REFUND";
889 
		            }
890 
		            double expectedCollection = totalSellingPrice - otherCollectionCharges - serviceTax;
891 
 
892 
		            //FIXME ignore differences of upto 50 paisa
893 
		            if (netCollection > 0.0 && expectedCollection != netCollection)	{
894 
		            	Map <String, Double> mismatch = new HashMap<String, Double>();
895 
		            	mismatch.put("expected", expectedCollection);
896 
		            	mismatch.put("actual", netCollection);
897 
		            	misMatches.put(order.getId(), mismatch);
898 
		            }
4600 varun.gupt 899 
 
4734 varun.gupt 900 
			    	Row contentRow = sheet.createRow(serialNo++);
901 
		            contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
902 
		            contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
903 
				    contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
904 
				    contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
905 
				    contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
906 
				    contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
907 
				    contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
908 
				    contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
909 
				    contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
910 
				    contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
911 
				    contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(transferPrice);
912 
				    contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
913 
				    contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(transferPrice * lineItem.getQuantity());
914 
				    contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(totalSellingPrice);
915 
				    contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatusDescription());
4715 varun.gupt 916 
 
4734 varun.gupt 917 
			    	double refund = 0.0;
4715 varun.gupt 918 
 
4734 varun.gupt 919 
				    if (payment != null)	{
920 
					    contentRow.createCell(OrderReportColumn.SETTLEMENT_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(paymentSettlement.getSettlementDate())));
921 
				    	contentRow.createCell(OrderReportColumn.GATEWAY_TRANSACTION_ID.getValue()).setCellValue(payment.getGatewayTxnId());
922 
				    	contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue(paymentType);
923 
				    	contentRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue(payment.getPaymentId());
924 
 
925 
				    	if(refunds.containsKey(payment.getPaymentId()))	{
926 
				    		refund = refunds.get(payment.getPaymentId()).getNetCollection();
927 
				    		refunds.remove(payment.getPaymentId());
928 
				    	}
929 
				    	contentRow.createCell(OrderReportColumn.REFUND.getValue()).setCellValue(refund);
930 
				    }
931 
				    contentRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue(txn.getCoupon_code());
932 
				    contentRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue(serviceTax);
933 
				    contentRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue(otherCollectionCharges);
934 
				    contentRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue(netCollection);
935 
				    contentRow.createCell(OrderReportColumn.FINAL_AMOUNT.getValue()).setCellValue(netCollection - refund);
936 
 
937 
				    boolean isReshippedOrder = reshippedOrderIds.contains(order.getId());
938 
				    contentRow.createCell(OrderReportColumn.IS_RESHIPED_ORDER.getValue()).setCellValue(isReshippedOrder ? 1 : 0);
939 
				}
940 
			}
941 
 
4715 varun.gupt 942 
		    logger.info("Order Ids where no successful payment was found: " + orderIdsWithoutSuccess.size());
943 
		    logger.info(orderIdsWithoutSuccess.toString());
944 
		    logger.info("Pending Refunds: " + refunds);
945 
		    logger.info("Reshipped Order Ids" + reshippedOrderIds);
946 
 
947 
	    	sheet.createRow(serialNo++);
948 
 
949 
		    // Refunds belonging to orders billed before the start date of supplied billing date range
950 
		    for (long paymentId: refunds.keySet())	{
951 
		    	Payment payment = paymentClient.getPayment(paymentId);
952 
		    	Transaction txn = transactionClient.getTransaction(payment.getMerchantTxnId());
953 
		    	PaymentSettlement paymentSettlement = refunds.get(paymentId);
954 
 
955 
		    	for (Order order: txn.getOrders())	{
4734 varun.gupt 956 
		    		if(!refundOrderStatuses.contains(order.getStatus()))	continue;
957 
 
4715 varun.gupt 958 
			    	Row contentRow = sheet.createRow(serialNo++);
959 
 
960 
			    	LineItem lineItem = order.getLineitems().get(0);
961 
				    double transferPrice = lineItem.getTransfer_price();
962 
		            double totalSellingPrice = lineItem.getTotal_price();
963 
		            String paymentType = paymentGateways.get(payment.getGatewayId()) + "-REFUND";
964 
 
965 
			    	contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
966 
		            contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
967 
				    contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
968 
				    contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
969 
				    contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
970 
				    contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
971 
				    contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
972 
				    contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
973 
				    contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
974 
				    contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
975 
				    contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(transferPrice);
976 
				    contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
977 
				    contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(transferPrice * lineItem.getQuantity());
978 
				    contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(totalSellingPrice);
979 
				    contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatusDescription());
980 
 
981 
				    contentRow.createCell(OrderReportColumn.SETTLEMENT_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(paymentSettlement.getSettlementDate())));
982 
			    	contentRow.createCell(OrderReportColumn.GATEWAY_TRANSACTION_ID.getValue()).setCellValue(payment.getGatewayTxnId());
983 
			    	contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue(paymentType);
984 
			    	contentRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue(payment.getPaymentId());
985 
			    	contentRow.createCell(OrderReportColumn.REFUND.getValue()).setCellValue(paymentSettlement.getNetCollection());
986 
 
987 
				    contentRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue(txn.getCoupon_code());
988 
				    contentRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue(0.0);
989 
				    contentRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue(0.0);
990 
				    contentRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue(paymentSettlement.getNetCollection());
991 
		    	}
992 
		    }
993 
 
4600 varun.gupt 994 
		} catch (TTransportException e) {
4715 varun.gupt 995 
			logger.error("TTransportException " + e);
4600 varun.gupt 996 
		} catch (PaymentException e) {
4715 varun.gupt 997 
			logger.error("PaymentException " + e);
4600 varun.gupt 998 
		} catch (TException e) {
4715 varun.gupt 999 
			logger.error("TException " + e);
4600 varun.gupt 1000 
		} catch (TransactionServiceException e) {
4734 varun.gupt 1001 
			logger.error("Transaction Service Exception " + e);
1002 
		} catch (LogisticsServiceException e) {
1003 
			addActionError("Error loading list of COD providers");
1004 
			logger.error("Logistics Service Exception " + e);
4600 varun.gupt 1005 
		}
1006 
	}
1007 
 
1008 
	public List<Vendor> getAllVendors()	{
1009 
		return this.vendors;
1010 
	}
1011 
 
1012 
	private String getValueForEmptyString(String s){
1013 
		if(s==null || s.equals(""))
1014 
			return "-";
1015 
		else
1016 
			return s;
1017 
	}
1018 
 
1019 
	private String getVendorName(long vendorId)	{
1020 
		for (Vendor vendor: vendors)	{
1021 
			if (vendor.getId() == vendorId)	return vendor.getName();
1022 
		}
1023 
		return null;
1024 
	}
1025 
 
1026 
	public String getReportSource()	{
1027 
		return reportSource;
1028 
	}
1029 
 
1030 
	public void setReportSource(String reportSource)	{
1031 
		this.reportSource = reportSource;
1032 
	}
1033 
 
1034 
    public File getHdfcSettlementReport() {
1035 
        return hdfcSettlementReport;
1036 
    }
1037 
 
1038 
    public void setHdfcSettlementReport(File hdfcSettlementReport) {
1039 
        this.hdfcSettlementReport = hdfcSettlementReport;
1040 
    }
1041 
 
1042 
    public File getEbsSettlementSummary()	{
1043 
    	return ebsSettlementSummary;
1044 
    }
1045 
 
1046 
    public void setEbsSettlementSummary(File ebsSettlementSummary)	{
1047 
    	this.ebsSettlementSummary = ebsSettlementSummary;
1048 
    }
1049 
 
1050 
    public File getEbsSettlementReport()	{
1051 
    	return ebsSettlementReport;
1052 
    }
1053 
 
1054 
    public void setEbsSettlementReport(File ebsSettlementReport)	{
1055 
    	this.ebsSettlementReport = ebsSettlementReport;
1056 
    }
1057 
 
1058 
    public String getBluedartSettlementDate()	{
1059 
    	return bluedartSettlementDate;
1060 
    }
1061 
 
1062 
    public void setBluedartSettlementDate(String bluedartSettlementDate)	{
1063 
    	this.bluedartSettlementDate = bluedartSettlementDate;
1064 
    }
1065 
 
1066 
    public File getBluedartSettlementReport()	{
1067 
    	return bluedartSettlementReport;
1068 
    }
1069 
 
1070 
    public void setBluedartSettlementReport(File bluedartSettlementReport)	{
1071 
    	this.bluedartSettlementReport = bluedartSettlementReport;
1072 
    }
1073 
 
4715 varun.gupt 1074 
    public String getAramexSettlementDate()	{
1075 
    	return this.aramexSettlementDate;
1076 
    }
1077 
 
1078 
    public void setAramexSettlementDate(String aramexSettlementDate)	{
1079 
    	this.aramexSettlementDate = aramexSettlementDate;
1080 
    }
1081 
 
1082 
    public void setAramexSettlementReport(File aramexSettlementReport) {
1083 
		this.aramexSettlementReport = aramexSettlementReport;
1084 
	}
1085 
 
1086 
	public File getAramexSettlementReport() {
1087 
		return aramexSettlementReport;
1088 
	}
1089 
 
4600 varun.gupt 1090 
	@Override
4715 varun.gupt 1091 
     public void setServletContext(ServletContext context) {
4600 varun.gupt 1092 
        this.context = context;
1093 
    }
1094 
 
1095 
    public String getServletContextPath() {
1096 
        return context.getContextPath();
1097 
    }
1098 
 
1099 
	@Override
1100 
	public void setServletResponse(HttpServletResponse response) {
1101 
		this.response = response;
1102 
	}
1103 
 
1104 
	@Override
1105 
	public void setServletRequest(HttpServletRequest request) {
1106 
		this.request = request;
1107 
		this.session = request.getSession();
1108 
	}
1109 
 
1110 
	public Map<Long, String> getEBSSettlementSummaries()	{
1111 
		return ebsSettlementSummaries;
1112 
	}
1113 
}