Subversion Repositories SmartDukaan

Rev

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