Subversion Repositories SmartDukaan

Rev

Rev 5441 | Rev 5945 | 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 
 
4734 varun.gupt 3 
import in.shop2020.logistics.Provider;
4600 varun.gupt 4 
import in.shop2020.model.v1.catalog.Vendor;
5 
import in.shop2020.model.v1.order.LineItem;
6 
import in.shop2020.model.v1.order.Order;
4734 varun.gupt 7 
import in.shop2020.model.v1.order.OrderStatus;
5020 varun.gupt 8 
import in.shop2020.model.v1.order.OrderStatusGroups;
4600 varun.gupt 9 
import in.shop2020.model.v1.order.PaymentSettlement;
5386 phani.kuma 10 
import in.shop2020.model.v1.order.ReturnOrder;
4600 varun.gupt 11 
import in.shop2020.model.v1.order.Transaction;
12 
import in.shop2020.model.v1.order.TransactionServiceException;
13 
import in.shop2020.payments.Payment;
14 
import in.shop2020.payments.PaymentGateway;
15 
import in.shop2020.thrift.clients.CatalogClient;
4734 varun.gupt 16 
import in.shop2020.thrift.clients.LogisticsClient;
4600 varun.gupt 17 
import in.shop2020.thrift.clients.PaymentClient;
18 
import in.shop2020.thrift.clients.TransactionClient;
19 
 
5020 varun.gupt 20 
import java.io.ByteArrayOutputStream;
21 
import java.io.File;
22 
import java.io.FileInputStream;
23 
import java.io.FileNotFoundException;
24 
import java.io.IOException;
25 
import java.text.DateFormat;
26 
import java.text.ParseException;
27 
import java.text.SimpleDateFormat;
28 
import java.util.ArrayList;
29 
import java.util.Date;
30 
import java.util.HashMap;
5386 phani.kuma 31 
import java.util.HashSet;
5020 varun.gupt 32 
import java.util.List;
33 
import java.util.Map;
5386 phani.kuma 34 
import java.util.Set;
35 
import java.util.Map.Entry;
5020 varun.gupt 36 
 
4600 varun.gupt 37 
import javax.servlet.ServletContext;
38 
import javax.servlet.ServletOutputStream;
39 
import javax.servlet.http.HttpServletRequest;
40 
import javax.servlet.http.HttpServletResponse;
5189 varun.gupt 41 
import org.apache.commons.io.IOUtils;
4600 varun.gupt 42 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
43 
import org.apache.poi.ss.usermodel.Row;
44 
import org.apache.poi.ss.usermodel.Sheet;
45 
import org.apache.poi.ss.usermodel.Workbook;
5020 varun.gupt 46 
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
4600 varun.gupt 47 
import org.apache.struts2.interceptor.ServletRequestAware;
48 
import org.apache.struts2.interceptor.ServletResponseAware;
49 
import org.apache.struts2.rest.DefaultHttpHeaders;
50 
import org.apache.struts2.rest.HttpHeaders;
51 
import org.apache.struts2.util.ServletContextAware;
52 
import org.apache.thrift.transport.TTransportException;
53 
import org.slf4j.Logger;
54 
import org.slf4j.LoggerFactory;
55 
 
4715 varun.gupt 56 
import com.opensymphony.xwork2.ValidationAwareSupport;
57 
 
4600 varun.gupt 58 
/**
59 
 * @author Varun Gupta
60 
 * @description: This class handles web requests to generate vendor specific reconciliation reports
61 
 *               for given vendor for a given date range
62 
 */
5386 phani.kuma 63 
 
4715 varun.gupt 64 
public class VendorReconciliationController extends ValidationAwareSupport implements ServletRequestAware, ServletResponseAware, ServletContextAware {
4600 varun.gupt 65 
 
5386 phani.kuma 66 
    private static final long serialVersionUID = 1L;
67 
	private static Logger logger = LoggerFactory.getLogger(VendorReconciliationController.class);
4600 varun.gupt 68 
 
69 
	private enum OrderReportColumn {
70 
	    ORDER_ID(0),
5386 phani.kuma 71 
	    ORIGINAL_ORDER_ID(1),
72 
	    VENDOR_ID(2),
73 
	    ORDER_DATE(3),
74 
		BILLING_NUMBER(4),
75 
		BILLING_DATE(5),
76 
		DELIVERY_DATE(6),
4600 varun.gupt 77 
		BRAND(7),
78 
		MODEL_NAME(8),
79 
		MODEL_NUMBER(9),
80 
		COLOR(10),
81 
		QUANTITY(11),
82 
		UNIT_TRANSFER_PRICE(12),
83 
		UNIT_SELLING_PRICE(13),
84 
		TOTAL_TRANSFER_PRICE(14),
85 
		TOTAL_SELLING_PRICE(15),
5386 phani.kuma 86 
		CURRENT_STATUS(16),
5481 phani.kuma 87 
		VENDOR_PAID(17),
88 
		PAYMENT_STATUS(18),
89 
		PAYMENT_TYPE(19),
90 
		PAYMENT_ID(20),
91 
		COUPON_CODE(21),
92 
		SETTLEMENT_DATE(22),
93 
		SERVICE_TAX(23),
94 
		OTHER_CHARGES(24),
95 
		NET_COLLECTION(25),
96 
		USER_PAYABLE_AMOUNT(26);
4600 varun.gupt 97 
 
98 
		private int value;
99 
 
100 
		OrderReportColumn(int value) {
101 
			this.value = value;
102 
		}
103 
		public int getValue(){
104 
			return this.value;
105 
		}
106 
	}
107 
 
108 
	private HttpServletRequest request;
109 
	private HttpServletResponse response;
110 
	private ServletContext context;
111 
	private List<Vendor> vendors;
5020 varun.gupt 112 
	private Map<Long, String> codProviders = new HashMap<Long, String>();
113 
	private Map<Long, String> paymentGateways = new HashMap<Long, String>();
114 
 
5386 phani.kuma 115 
	private File orderIdsToBeMarked;
4600 varun.gupt 116 
	private File hdfcSettlementReport;
117 
 
118 
	private File ebsSettlementReport;
119 
	private File ebsSettlementSummary;
120 
 
121 
	private String bluedartSettlementDate;
122 
	private File bluedartSettlementReport;
123 
 
4715 varun.gupt 124 
	private String aramexSettlementDate;
125 
	private File aramexSettlementReport;
126 
 
5481 phani.kuma 127 
	private String delhiverySettlementDate;
128 
	private File delhiverySettlementReport;
129 
 
4600 varun.gupt 130 
	private Map<Long, String> ebsSettlementSummaries;
131 
 
5020 varun.gupt 132 
	private List<OrderStatus> refundOrderStatuses;
5189 varun.gupt 133 
 
5386 phani.kuma 134 
	private List<Order> reshippedNonPayableOrders = new ArrayList<Order>();
135 
	private List<Order> returnedOrders = new ArrayList<Order>();
5189 varun.gupt 136 
	private List<Order> unsettledOrders = new ArrayList<Order>();
5386 phani.kuma 137 
	private List<Order> userPaymentNotRequiredOrders = new ArrayList<Order>();
138 
	private Map<Order, PaymentSettlement> paymentSettledOrdersMap = new HashMap<Order, PaymentSettlement>();
139 
	private List<Order> ordersWithErrors = new ArrayList<Order>();
4745 varun.gupt 140 
 
4600 varun.gupt 141 
	private final DateFormat DATE_FORMAT = new SimpleDateFormat("dd/MM/yyyy");
142 
 
143 
	public String index()	{
5098 varun.gupt 144 
		initiatializeDashboard();
4600 varun.gupt 145 
		return "report";
146 
	}
147 
 
148 
	public HttpHeaders create()	{
149 
 
150 
		String formType = request.getParameter("formtype");
4715 varun.gupt 151 
		logger.info("Form Type: " + formType);
4600 varun.gupt 152 
 
153 
		if (formType.equals("uploadEBSSettlementSummary")) {
154 
			uploadEBSSettlementSummary();
155 
 
156 
		} else if (formType.equals("uploadEBSSettlements")) {
157 
			uploadEBSSettlements();
158 
 
159 
		} else if (formType.equals("uploadHDFCSettlements")) {
160 
			uploadHDFCSettlements();
161 
 
162 
		} else if (formType.equals("uploadBluedartSettlements")) {
163 
			uploadBluedartSettlements();
4715 varun.gupt 164 
 
165 
		} else if (formType.equals("uploadAramexSettlements")) {
166 
			uploadAramexSettlements();
5098 varun.gupt 167 
 
168 
		} else if (formType.equals("markOrdersAsPaid"))	{
5189 varun.gupt 169 
			uploadOrderIdsToBeMarkedPaid();
5386 phani.kuma 170 
 
171 
		} else if (formType.equals("markOrdersAsReturned"))	{
172 
			uploadOrderIdsToBeMarkedReturned();
173 
 
5481 phani.kuma 174 
		} else if (formType.equals("uploadDelhiverySettlements"))	{
175 
			uploadDelhiverySettlements();
176 
 
177 
		}
5386 phani.kuma 178 
 
5098 varun.gupt 179 
		initiatializeDashboard();
4600 varun.gupt 180 
		return new DefaultHttpHeaders("report");
181 
	}
182 
 
183 
	public HttpHeaders generateReconciliationReport()	{
5189 varun.gupt 184 
		initiatializeDashboard();
5386 phani.kuma 185 
 
186 
		try {
187 
			LogisticsClient lsc = new LogisticsClient();
188 
	    	in.shop2020.logistics.LogisticsService.Client logisticsClient = lsc.getClient();
189 
 
190 
	    	PaymentClient psc = new PaymentClient();
191 
	    	in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
192 
 
193 
			List<PaymentGateway> tPaymentGateways = paymentClient.getActivePaymentGateways();
194 
 
195 
	    	for (PaymentGateway tPaymentGateway: tPaymentGateways)	{
196 
	    		paymentGateways.put(tPaymentGateway.getId(), tPaymentGateway.getName());
197 
	    	}
198 
 
199 
	    	for (Provider provider: logisticsClient.getAllProviders())	{
200 
	    		codProviders.put(provider.getId(), provider.getName());
201 
	    	}
202 
 
203 
			try	{
5481 phani.kuma 204 
				DateFormat dateFormat = new SimpleDateFormat("dd/MM/yyyy");
205 
				String formType = request.getParameter("formtype");
206 
				long fromTime = 0;
207 
				long toTime = 0;
208 
				boolean notall = true;
209 
 
210 
				if (formType.equals("downloadbydate")) {
211 
					if(! request.getParameter("start").isEmpty() && ! request.getParameter("end").isEmpty()) {
212 
						Date startDate = dateFormat.parse(request.getParameter("start"));
213 
					    fromTime = startDate.getTime();
214 
 
215 
					    Date endDate = dateFormat.parse(request.getParameter("end"));
216 
					    toTime = endDate.getTime();
217 
					}
218 
					else {
219 
						String errMsg = "No Date Range given";
220 
						logger.error(errMsg);
221 
						addActionError(errMsg);
222 
						return new DefaultHttpHeaders("report");
223 
					}
224 
					notall = false;
225 
 
226 
				}
227 
 
5386 phani.kuma 228 
			    long vendorId = Long.parseLong(request.getParameter("vendor"));
229 
			    String vendorName = "";
230 
			    logger.info("Vendor ID: " + vendorId);
231 
 
232 
			    if(vendorId == -1) {
233 
			    	vendorName = "ALL-Vendors";
234 
			    }
235 
			    else {
236 
			    	vendorName = getVendorName(vendorId);
237 
			    	if(vendorName != null) {
238 
			    		vendorName = vendorName.replaceAll("\\s", "-");
239 
			    	}
240 
			    }
241 
 
242 
				response.setContentType("application/vnd.ms-excel");
243 
				response.setHeader("Content-disposition", "inline; filename=" + vendorName + "-reconciliation.xls");
244 
 
245 
				ServletOutputStream sos;
246 
				try {
5481 phani.kuma 247 
					ByteArrayOutputStream baos = getReconciliationReport(vendorId, fromTime, toTime, notall);
5386 phani.kuma 248 
					sos = response.getOutputStream();
249 
					baos.writeTo(sos);
250 
					sos.flush();
251 
				} catch (IOException e)	{
252 
					String errMsg = "Error while streaming the reconciliation report";
253 
					logger.error(errMsg, e);
254 
					addActionError(errMsg);
255 
				}
256 
			} catch (NullPointerException e) {
257 
	            String errMsg = "NullPointerException";
258 
				logger.error(errMsg, e);
259 
				addActionError(errMsg);
5481 phani.kuma 260 
			} catch (ParseException e) {
261 
	        	String errMsg = "Could not parse the Dates";
262 
	        	logger.error(errMsg, e);
263 
				addActionError(errMsg);
264 
	        }
4600 varun.gupt 265 
 
5386 phani.kuma 266 
		} catch (Exception e) {
267 
			String errMsg = "Exception Occured in generateReconciliationReport";
268 
			logger.error(errMsg, e);
269 
			addActionError(errMsg);
4745 varun.gupt 270 
		}
5386 phani.kuma 271 
 
4600 varun.gupt 272 
		return new DefaultHttpHeaders("report");
273 
	}
274 
 
275 
	public void uploadEBSSettlementSummary()	{
276 
 
277 
		DateFormat settlementDateFormat = new SimpleDateFormat("dd MMM,yyyy hh:mm:ss a");
278 
		DateFormat transactionDateFormat = new SimpleDateFormat("yyyy-MM-dd");
279 
 
280 
		Workbook wb = null;
281 
        try {
282 
            wb = new HSSFWorkbook(new FileInputStream(ebsSettlementSummary));
283 
        } catch (FileNotFoundException e) {
5386 phani.kuma 284 
        	String errMsg = "Unable to open the EBS Settlement Summary report";
285 
			logger.error(errMsg, e);
286 
			addActionError(errMsg);
287 
			return;
4600 varun.gupt 288 
        } catch (IOException e) {
5386 phani.kuma 289 
        	String errMsg = "Unable to open the EBS Settlement Summary report";
290 
			logger.error(errMsg, e);
291 
			addActionError(errMsg);
292 
			return;
4600 varun.gupt 293 
        }
294 
        Sheet sheet = wb.getSheetAt(0);
295 
 
296 
        int indexSettlementId = 0;
297 
        int indexSettlementDate = 1;
298 
        int indexTransactionDateFrom = 2;
299 
        int indexTransactionDateTo = 3;
300 
        int indexAmount = 4;
301 
 
302 
		TransactionClient tsc;
5481 phani.kuma 303 
		int countSuccessfulSaves = 0;
5386 phani.kuma 304 
		long settlementId = 0;
4600 varun.gupt 305 
 
306 
		try {
307 
			tsc = new TransactionClient();
308 
			in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
309 
 
310 
			for(Row row: sheet)	{
5481 phani.kuma 311 
				if(row.getRowNum() < 5) {
312 
	    			continue;
313 
	    		}
4600 varun.gupt 314 
 
315 
				try	{
5481 phani.kuma 316 
					settlementId = (long) row.getCell(indexSettlementId).getNumericCellValue();
317 
					String settlementDateStr = row.getCell(indexSettlementDate).getStringCellValue();
318 
					String transactionDateFromStr = row.getCell(indexTransactionDateFrom).getStringCellValue();
319 
					String transactionDateToStr = row.getCell(indexTransactionDateTo).getStringCellValue();
320 
					Double amount = row.getCell(indexAmount).getNumericCellValue();
321 
 
322 
					Date settlementDate = settlementDateFormat.parse(settlementDateStr);
323 
					Date transactionDateFrom = transactionDateFormat.parse(transactionDateFromStr);
324 
					Date transactionDateTo = transactionDateFormat.parse(transactionDateToStr);
325 
 
4600 varun.gupt 326 
					transactionClient.saveEBSSettlementSummary(settlementId, settlementDate.getTime(), transactionDateFrom.getTime(), transactionDateTo.getTime(), amount);
5481 phani.kuma 327 
					countSuccessfulSaves += 1;
4600 varun.gupt 328 
 
5386 phani.kuma 329 
				} catch (TransactionServiceException e) {
330 
                	String errMsg = "TransactionServiceException recieved for settlement Id, " + Long.toString(settlementId);
331 
        			logger.error(errMsg, e);
332 
        			addActionError(errMsg);
5481 phani.kuma 333 
        		} catch (Exception e) {
334 
                	String errMsg = "Exception recieved for settlement Id, " + Long.toString(settlementId);
335 
        			logger.error(errMsg, e);
336 
        			addActionError(errMsg);
5386 phani.kuma 337 
        		}
4600 varun.gupt 338 
			}
339 
        } catch (Exception e) {
5481 phani.kuma 340 
			String errMsg = "Exception recieved for settlement Id," + Long.toString(settlementId);
5386 phani.kuma 341 
			logger.error(errMsg, e);
342 
			addActionError(errMsg);
4600 varun.gupt 343 
		}
5481 phani.kuma 344 
        addActionMessage("Successfully added " + countSuccessfulSaves + " EBS Settlements");
4600 varun.gupt 345 
	}
346 
 
347 
	public void uploadHDFCSettlements()	{
348 
		logger.info("Uploading HDFC settlements");
349 
 
350 
		Workbook wb = null;
351 
		try {
352 
			wb = new HSSFWorkbook(new FileInputStream(this.hdfcSettlementReport));
353 
		} catch (FileNotFoundException e) {
4734 varun.gupt 354 
			String errMsg = "Unable to open the HDFC Payout report";
355 
			logger.error(errMsg, e);
356 
			addActionError(errMsg);
357 
			return;
4600 varun.gupt 358 
		} catch (IOException e) {
4734 varun.gupt 359 
			String errMsg = "Unable to open the HDFC Payout report";
360 
			logger.error(errMsg, e);
361 
			addActionError(errMsg);
362 
			return;
4600 varun.gupt 363 
		}
364 
		Sheet sheet = wb.getSheetAt(0);
365 
 
366 
		int indexRECFMT = 2;
367 
		int indexSettlementDate = 7;
368 
		int indexPaymentId = 13;
369 
		int indexMSF = 14;
370 
		int indexServiceTax = 15;
371 
		int indexEduCess = 16;
372 
		int indexNetCollection = 17;
373 
 
374 
		TransactionClient tsc;
375 
		String paymentIdStr = "";
376 
		long paymentId = 0;
5020 varun.gupt 377 
 
4734 varun.gupt 378 
		int countSuccessfulInserts = 0;
379 
		int countRefunds = 0;
4600 varun.gupt 380 
 
381 
		try {
382 
			tsc = new TransactionClient();
383 
			in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
384 
 
385 
			for (Row row: sheet)	{
5481 phani.kuma 386 
				if(row.getRowNum() < 1) {
387 
	    			continue;
388 
	    		}
4600 varun.gupt 389 
 
390 
                try	{
5481 phani.kuma 391 
 
392 
                	if(row.getCell(0).getStringCellValue().equals("Total")) {
393 
                    	break;
394 
                    }
395 
 
4600 varun.gupt 396 
                    String recfmt = row.getCell(indexRECFMT).getStringCellValue();
397 
                    Date settlementDate = row.getCell(indexSettlementDate).getDateCellValue();
398 
                	double netCollection = row.getCell(indexNetCollection).getNumericCellValue();
5020 varun.gupt 399 
                	paymentIdStr = row.getCell(indexPaymentId).getStringCellValue().replace("'", "").trim();
4600 varun.gupt 400 
                	paymentId = Long.parseLong(paymentIdStr);
401 
 
402 
                	logger.info("paymentId: " + paymentId + ", recfmt: " + recfmt + ", settlementDate: " + settlementDate + ", netCollection: " + netCollection);
403 
 
4715 varun.gupt 404 
                    if (recfmt.trim().equalsIgnoreCase("CVD"))	{
405 
                    	if (netCollection > 0.0)	netCollection *= -1.0;
406 
                    	transactionClient.savePaymentSettlements(settlementDate.getTime(), 1, paymentId, 0.0, 0.0, netCollection);
4734 varun.gupt 407 
                    	countRefunds += 1;
5020 varun.gupt 408 
 
4600 varun.gupt 409 
                    } else	{
410 
                    	double msf = row.getCell(indexMSF).getNumericCellValue();
411 
                    	double serviceTax = row.getCell(indexServiceTax).getNumericCellValue();
412 
                    	double eduCess = row.getCell(indexEduCess).getNumericCellValue();
413 
                    	logger.info("msf: " + msf + ", serviceTax: " + serviceTax + ", eduCess: " + eduCess);
414 
 
415 
                    	transactionClient.savePaymentSettlements(settlementDate.getTime(), 1, paymentId, serviceTax, (eduCess + msf), netCollection);
4734 varun.gupt 416 
                    	countSuccessfulInserts += 1;
4600 varun.gupt 417 
                 	}
418 
                } catch (NumberFormatException e) {
4734 varun.gupt 419 
                	String errMsg = "NumberFormatException recieved for payment Id, " + paymentIdStr;
5386 phani.kuma 420 
        			logger.error(errMsg, e);
4734 varun.gupt 421 
        			addActionError(errMsg);
4600 varun.gupt 422 
 
423 
        		} catch (TransactionServiceException e) {
5386 phani.kuma 424 
                	String errMsg = "TransactionServiceException recieved for payment Id, " + Long.toString(paymentId);
425 
        			logger.error(errMsg, e);
4734 varun.gupt 426 
        			addActionError(errMsg);
5481 phani.kuma 427 
        		} catch (Exception e) {
428 
        			String errMsg = "Exception recieved for payment Id," + Long.toString(paymentId);
429 
        			logger.error(errMsg, e);
430 
        			addActionError(errMsg);
4600 varun.gupt 431 
        		}
432 
			}
5386 phani.kuma 433 
		} catch (Exception e) {
434 
			String errMsg = "Exception recieved for payment Id," + Long.toString(paymentId);
435 
			logger.error(errMsg, e);
436 
			addActionError(errMsg);
437 
		}
4600 varun.gupt 438 
 
4734 varun.gupt 439 
		addActionMessage("Added settlements for " + countSuccessfulInserts + " payments");
440 
		addActionMessage("Added settlements for " + countRefunds + " refund payments");
4600 varun.gupt 441 
	}
442 
 
443 
	public void uploadEBSSettlements()	{
444 
		Workbook wb = null;
445 
        try {
446 
            wb = new HSSFWorkbook(new FileInputStream(this.ebsSettlementReport));
447 
        } catch (FileNotFoundException e) {
5386 phani.kuma 448 
        	String errMsg = "Unable to open the EBS Settlement detail report";
449 
			logger.error(errMsg, e);
450 
			addActionError(errMsg);
451 
			return;
4600 varun.gupt 452 
        } catch (IOException e) {
5386 phani.kuma 453 
        	String errMsg = "Unable to open the EBS Settlement detail report";
454 
			logger.error(errMsg, e);
455 
			addActionError(errMsg);
456 
			return;
4600 varun.gupt 457 
        }
458 
 
459 
        Sheet sheet = wb.getSheetAt(0);
460 
 
461 
		Row firstRow = sheet.getRow(0);
462 
		Row secondRow = sheet.getRow(1);
463 
 
464 
        int indexPaymentId = 2;
465 
        int indexTxnType = 5;
466 
        int indexAmount = 6;
467 
 
468 
        Map<Long, Map<String, Double>> map = new HashMap<Long, Map<String,Double>>();
469 
        Map <String, Double> record;
4734 varun.gupt 470 
        int countPaymentIds = 0;
4600 varun.gupt 471 
 
472 
        try	{
473 
        	for (Row row: sheet)	{
474 
                if(row.equals(firstRow) || row.equals(secondRow))	continue;
475 
 
476 
                long paymentId = Long.parseLong(row.getCell(indexPaymentId).getStringCellValue());
477 
                String transactionType = row.getCell(indexTxnType).getStringCellValue();
478 
                double amount = row.getCell(indexAmount).getNumericCellValue();
479 
 
480 
                if(map.containsKey(paymentId))	{
481 
                	map.get(paymentId).put(transactionType, amount);
482 
 
483 
                } else	{
484 
                	record = new HashMap<String, Double>();
485 
                	record.put(transactionType, amount);
486 
                	map.put(paymentId, record);
4734 varun.gupt 487 
                	countPaymentIds += 1;
4600 varun.gupt 488 
                }
489 
        	}
490 
        } catch	(NullPointerException e)	{
491 
        	logger.error("" + e);
492 
        }
4734 varun.gupt 493 
        addActionMessage("Total Payment Ids added: " + countPaymentIds);
4600 varun.gupt 494 
 
495 
		TransactionClient tsc;
496 
        long settlementId = Long.parseLong(request.getParameter("settlementId"));
497 
 
4734 varun.gupt 498 
        int countPaymentsRecieved = 0;
499 
        int countPaymentsRefunded = 0;
500 
 
4600 varun.gupt 501 
        try	{
502 
			tsc = new TransactionClient();
503 
			in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
4734 varun.gupt 504 
			long settlementDate;
4600 varun.gupt 505 
 
4734 varun.gupt 506 
			try	{
507 
				settlementDate = transactionClient.getEBSSettlementDate(settlementId);
508 
			} catch (TransactionServiceException e) {
509 
				String errMsg = "Could not retrieve EBS Settlement date for settlementId, " + settlementId;
5386 phani.kuma 510 
	        	logger.error(errMsg, e);
4734 varun.gupt 511 
	        	addActionError(errMsg);
512 
	        	return;
513 
			}
514 
 
4600 varun.gupt 515 
        	for (long paymentId: map.keySet())	{
516 
            	record = map.get(paymentId);
517 
 
518 
            	if(record.containsKey("Captured"))	{
519 
            		double capturedAmount = record.get("Captured");
520 
            		double tdr = record.get("TDR");
521 
            		double serviceTax = record.get("ServiceTax");
522 
 
523 
            		if(tdr < 0)	tdr *= -1;
524 
            		if(serviceTax < 0)	serviceTax *= -1;
525 
 
526 
            		double amount = capturedAmount - tdr - serviceTax;
4734 varun.gupt 527 
 
528 
            		try	{
529 
            			transactionClient.savePaymentSettlements(settlementDate, 2, paymentId, serviceTax, tdr, amount);
530 
        			} catch (TransactionServiceException e) {
531 
        				String errMsg = "Could not save settlement for paymentId, " + paymentId;
532 
        	        	logger.error(errMsg + e);
533 
        	        	addActionError(errMsg);
534 
        			}
535 
            		countPaymentsRecieved += 1;
536 
            	}
537 
 
538 
            	if(record.containsKey("Refunded"))	{
4600 varun.gupt 539 
            		double refundedAmount = record.get("Refunded");
540 
 
541 
            		if (refundedAmount > 0)	refundedAmount *= -1;
542 
 
4734 varun.gupt 543 
            		try	{
544 
            			transactionClient.savePaymentSettlements(settlementDate, 2, paymentId, 0.0, 0.0, refundedAmount);
545 
        			} catch (TransactionServiceException e) {
546 
        				String errMsg = "Could not save refund settlement for paymentId, " + paymentId;
5386 phani.kuma 547 
        	        	logger.error(errMsg, e);
4734 varun.gupt 548 
        	        	addActionError(errMsg);
549 
        			}
550 
            		countPaymentsRefunded += 1;
4600 varun.gupt 551 
            	}
552 
            }
4734 varun.gupt 553 
        	addActionMessage("Total count of payments recieved: " + countPaymentsRecieved);
554 
        	addActionMessage("Total count of payments refunded: " + countPaymentsRefunded);
4600 varun.gupt 555 
 
4734 varun.gupt 556 
        	try	{
557 
        		transactionClient.markEBSSettlementUploaded(settlementId);
558 
			} catch (TransactionServiceException e) {
559 
				String errMsg = "Could not mark settlement for details Uploaded. Settlement ID: " + settlementId;
5386 phani.kuma 560 
	        	logger.error(errMsg, e);
4734 varun.gupt 561 
	        	addActionError(errMsg);
562 
			}
5386 phani.kuma 563 
        } catch (Exception e) {
564 
			String errMsg = e.getMessage();
565 
			logger.error(errMsg, e);
566 
			addActionError(errMsg);
4600 varun.gupt 567 
		}
568 
	}
569 
 
570 
	public void uploadBluedartSettlements()	{
571 
 
572 
		Workbook wb = null;
573 
        try {
574 
            wb = new HSSFWorkbook(new FileInputStream(this.bluedartSettlementReport));
575 
        } catch (FileNotFoundException e) {
5386 phani.kuma 576 
        	String errMsg = "Unable to open the Bluedart Settlement report";
577 
			logger.error(errMsg, e);
578 
			addActionError(errMsg);
579 
			return;
4600 varun.gupt 580 
        } catch (IOException e) {
5386 phani.kuma 581 
        	String errMsg = "Unable to open the Bluedart Settlement report";
582 
			logger.error(errMsg, e);
583 
			addActionError(errMsg);
584 
			return;
4600 varun.gupt 585 
        }
586 
 
587 
        Sheet sheet = wb.getSheetAt(0);
588 
 
5098 varun.gupt 589 
        int indexOrderId = 15;
590 
        int indexAmount = 14;
4600 varun.gupt 591 
 
592 
        TransactionClient tsc;
4734 varun.gupt 593 
    	int countSuccessfulSaves = 0;
4600 varun.gupt 594 
 
595 
        try	{
596 
			long settlementDate = DATE_FORMAT.parse(bluedartSettlementDate).getTime();
597 
        	tsc = new TransactionClient();
598 
        	in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
599 
 
5386 phani.kuma 600 
        	long orderId = 0;
601 
 
4600 varun.gupt 602 
        	for (Row row : sheet) {
5098 varun.gupt 603 
    			String orderIdString = "";
5481 phani.kuma 604 
 
605 
    			if(row.getRowNum() < 1) {
606 
	    			continue;
607 
	    		}
608 
 
4600 varun.gupt 609 
        		try	{
5098 varun.gupt 610 
        			orderIdString = row.getCell(indexOrderId).getStringCellValue();
611 
        			orderId = Long.parseLong(orderIdString);
4715 varun.gupt 612 
 
4600 varun.gupt 613 
        			double amount = row.getCell(indexAmount).getNumericCellValue();
5020 varun.gupt 614 
        			logger.info("Bluedart SettlementDate: " + settlementDate + ", OrderId: " + orderId + ", Amount: " + amount);
4905 varun.gupt 615 
        			transactionClient.savePaymentSettlements(settlementDate, 4, orderId, 0.0, 0.0, amount);
4875 varun.gupt 616 
        			countSuccessfulSaves += 1;
4600 varun.gupt 617 
 
5098 varun.gupt 618 
        		} catch (IllegalStateException e) {
5386 phani.kuma 619 
        			String errMsg = "IllegalStateException Near OrderId: " + orderId;
620 
                	logger.error(errMsg, e);
621 
        			addActionError(errMsg);
5098 varun.gupt 622 
 
5386 phani.kuma 623 
        		} catch(NumberFormatException e) {
624 
        			String errMsg = "NumberFormatException for Extracted String: " + orderIdString;
625 
                	logger.error(errMsg, e);
626 
        			addActionError(errMsg);
5098 varun.gupt 627 
 
4600 varun.gupt 628 
        		} catch (NullPointerException e) {
5386 phani.kuma 629 
        			String errMsg = "NullPointerException For OrderId: " + orderId;
630 
                	logger.error(errMsg, e);
631 
        			addActionError(errMsg);
4600 varun.gupt 632 
 
4875 varun.gupt 633 
        		} catch (TransactionServiceException e) {
5386 phani.kuma 634 
        			String errMsg = "TransactionServiceException For OrderId: " + orderId;
635 
                	logger.error(errMsg, e);
636 
        			addActionError(errMsg);
5481 phani.kuma 637 
				} catch (Exception e) {
638 
					String errMsg = "Exception recieved for OrderId:" + orderId;
639 
					logger.error(errMsg, e);
640 
					addActionError(errMsg);
641 
		        }
4600 varun.gupt 642 
        	}
643 
        } catch (ParseException e) {
5386 phani.kuma 644 
        	String errMsg = "Could not parse " + bluedartSettlementDate;
645 
			logger.error(errMsg, e);
646 
			addActionError(errMsg);
647 
		} catch (Exception e) {
648 
			String errMsg = e.getMessage();
649 
			logger.error(errMsg, e);
650 
			addActionError(errMsg);
4600 varun.gupt 651 
        }
4734 varun.gupt 652 
        addActionMessage("Successfully added Bluedart settlement details for " + countSuccessfulSaves + " orders");
4600 varun.gupt 653 
	}
654 
 
5386 phani.kuma 655 
	public void uploadAramexSettlements() {
4715 varun.gupt 656 
		logger.info("Uploading Aramex Settlements" + this.aramexSettlementDate);
657 
 
5386 phani.kuma 658 
		if(this.aramexSettlementDate.equals("")) {
4715 varun.gupt 659 
			addActionError("Settlement date cannot be left blank");
660 
			return;
661 
		}
662 
 
663 
		Workbook wb = null;
664 
        try {
5020 varun.gupt 665 
        	wb = new XSSFWorkbook(new FileInputStream(this.aramexSettlementReport));
4715 varun.gupt 666 
        } catch (FileNotFoundException e) {
5386 phani.kuma 667 
        	String errMsg = "Unable to open the Aramex Settlement report";
668 
			logger.error(errMsg, e);
669 
			addActionError(errMsg);
670 
			return;
4715 varun.gupt 671 
        } catch (IOException e) {
5386 phani.kuma 672 
        	String errMsg = "Unable to open the Aramex Settlement report";
673 
			logger.error(errMsg, e);
674 
			addActionError(errMsg);
675 
			return;
676 
        }
4715 varun.gupt 677 
 
678 
        Sheet sheet = wb.getSheetAt(0);
679 
 
5343 phani.kuma 680 
        int indexAWB = 0;
4734 varun.gupt 681 
        int indexAmount = 1;
5343 phani.kuma 682 
        int indexComment = 2;
4715 varun.gupt 683 
 
684 
        TransactionClient tsc;
4734 varun.gupt 685 
        int countSuccessfulSaves = 0;
4715 varun.gupt 686 
        try	{
687 
			long settlementDate = DATE_FORMAT.parse(aramexSettlementDate).getTime();
688 
 
689 
        	tsc = new TransactionClient();
690 
        	in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
691 
 
692 
	    	for (Row row: sheet)	{
5481 phani.kuma 693 
	    		if(row.getRowNum() < 1) {
5343 phani.kuma 694 
	    			continue;
695 
	    		}
4715 varun.gupt 696 
 
5441 phani.kuma 697 
	    		if(row.getCell(indexAWB) == null) {
698 
	    			continue;
699 
	    		}
700 
 
5343 phani.kuma 701 
				long awb = -1;
702 
 
5481 phani.kuma 703 
				try {
704 
					if(row.getCell(indexAWB).getCellType() == 0) {
705 
						awb = (long) row.getCell(indexAWB).getNumericCellValue();
706 
					}
707 
				    else if(row.getCell(indexAWB).getCellType() == 1) {
708 
				    	awb = Long.parseLong(row.getCell(indexAWB).getStringCellValue());
709 
				    }
710 
 
711 
					if(awb == -1) {
712 
						continue;
713 
					}
714 
		    		else if(row.getCell(indexComment).getStringCellValue().equals("LOST SHIPMENT")) {
715 
		    			logger.info("LOST SHIPMENT");
716 
		    			continue;
717 
		    		}
718 
 
719 
		    		long orderId = transactionClient.getOrderForAwb(Long.toString(awb)).getId();
720 
		    		double amount = row.getCell(indexAmount).getNumericCellValue();
721 
		    		logger.info("Aramex Settlement, Order Id: " + orderId + " Amount: " + amount);
722 
 
723 
					transactionClient.savePaymentSettlements(settlementDate, 4, orderId, 0.0, 0.0, amount);
724 
					countSuccessfulSaves += 1;
725 
 
726 
				} catch (TransactionServiceException e) {
727 
					logger.error("Transaction Service Exception while getting order for AWB, " + awb, e);
728 
					addActionError("Transaction Service Exception while getting order for AWB, " + awb);
729 
				} catch (Exception e) {
730 
					String errMsg = "Exception recieved for AWB," + awb;
731 
					logger.error(errMsg, e);
732 
					addActionError(errMsg);
5108 varun.gupt 733 
				}
5481 phani.kuma 734 
	    	}
735 
        } catch (ParseException e) {
736 
        	String errMsg = "Could not parse " + aramexSettlementDate;
737 
        	logger.error(errMsg, e);
738 
			addActionError(errMsg);
739 
        } catch (Exception e) {
740 
        	String errMsg = e.getMessage();
741 
			logger.error(errMsg, e);
742 
			addActionError(errMsg);
743 
        }
744 
        addActionMessage("Successfully added Aramex settlement details for " + countSuccessfulSaves + " orders");
745 
	}
746 
 
747 
	public void uploadDelhiverySettlements() {
748 
		logger.info("Uploading Delhivery Settlements" + this.delhiverySettlementDate);
749 
 
750 
		if(this.delhiverySettlementDate.equals("")) {
751 
			addActionError("Settlement date cannot be left blank");
752 
			return;
753 
		}
754 
 
755 
		Workbook wb = null;
756 
        try {
757 
        	wb = new XSSFWorkbook(new FileInputStream(this.delhiverySettlementReport));
758 
        } catch (FileNotFoundException e) {
759 
        	String errMsg = "Unable to open the Delhivery Settlement report";
760 
			logger.error(errMsg, e);
761 
			addActionError(errMsg);
762 
			return;
763 
        } catch (IOException e) {
764 
        	String errMsg = "Unable to open the Delhivery Settlement report";
765 
			logger.error(errMsg, e);
766 
			addActionError(errMsg);
767 
			return;
768 
        }
769 
 
770 
        Sheet sheet = wb.getSheetAt(0);
771 
 
772 
        int indexAWB = 1;
773 
        int indexAmount = 13;
774 
 
775 
        TransactionClient tsc;
776 
        int countSuccessfulSaves = 0;
777 
        try	{
778 
			long settlementDate = DATE_FORMAT.parse(delhiverySettlementDate).getTime();
779 
 
780 
        	tsc = new TransactionClient();
781 
        	in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
782 
 
783 
	    	for (Row row: sheet)	{
784 
	    		if(row.getRowNum() < 9) {
5343 phani.kuma 785 
	    			continue;
786 
	    		}
5481 phani.kuma 787 
 
788 
	    		if(row.getCell(indexAWB) == null) {
789 
	    			continue;
790 
	    		}
791 
 
792 
				long awb = -1;
5343 phani.kuma 793 
 
5481 phani.kuma 794 
				try {
795 
					if(row.getCell(indexAWB).getCellType() == 0) {
796 
						awb = (long) row.getCell(indexAWB).getNumericCellValue();
797 
					}
798 
				    else if(row.getCell(indexAWB).getCellType() == 1) {
799 
				    	awb = Long.parseLong(row.getCell(indexAWB).getStringCellValue());
800 
				    }
801 
 
802 
					if(awb == -1) {
803 
						continue;
804 
					}
805 
 
5108 varun.gupt 806 
		    		long orderId = transactionClient.getOrderForAwb(Long.toString(awb)).getId();
807 
		    		double amount = row.getCell(indexAmount).getNumericCellValue();
5481 phani.kuma 808 
		    		logger.info("Delhivery Settlement, Order Id: " + orderId + " Amount: " + amount);
5108 varun.gupt 809 
 
4907 varun.gupt 810 
					transactionClient.savePaymentSettlements(settlementDate, 4, orderId, 0.0, 0.0, amount);
4734 varun.gupt 811 
					countSuccessfulSaves += 1;
4715 varun.gupt 812 
 
813 
				} catch (TransactionServiceException e) {
5108 varun.gupt 814 
					logger.error("Transaction Service Exception while getting order for AWB, " + awb, e);
815 
					addActionError("Transaction Service Exception while getting order for AWB, " + awb);
5481 phani.kuma 816 
				} catch (Exception e) {
817 
					String errMsg = "Exception recieved for AWB," + awb;
818 
					logger.error(errMsg, e);
819 
					addActionError(errMsg);
4715 varun.gupt 820 
				}
821 
	    	}
822 
        } catch (ParseException e) {
5481 phani.kuma 823 
        	String errMsg = "Could not parse " + delhiverySettlementDate;
5386 phani.kuma 824 
        	logger.error(errMsg, e);
825 
			addActionError(errMsg);
826 
        } catch (Exception e) {
827 
        	String errMsg = e.getMessage();
828 
			logger.error(errMsg, e);
829 
			addActionError(errMsg);
4715 varun.gupt 830 
        }
5481 phani.kuma 831 
        addActionMessage("Successfully added Delhivery settlement details for " + countSuccessfulSaves + " orders");
4715 varun.gupt 832 
	}
833 
 
5098 varun.gupt 834 
	public void uploadOrderIdsToBeMarkedPaid()	{
835 
		logger.info("Uploading Order Ids to be marked as paid");
5189 varun.gupt 836 
		List<Long> orderIds = new ArrayList<Long>();
837 
 
838 
		try {
5386 phani.kuma 839 
			FileInputStream uploadedFile = new FileInputStream(this.orderIdsToBeMarked);
5189 varun.gupt 840 
			String uploadedContent = IOUtils.toString(uploadedFile);
841 
 
842 
			for(String s: uploadedContent.trim().split("\n"))	{
843 
 
5386 phani.kuma 844 
				if(! s.trim().isEmpty()) {
845 
					orderIds.add(Long.parseLong(s));
846 
				}
5189 varun.gupt 847 
			}
848 
		} catch (FileNotFoundException e) {
5386 phani.kuma 849 
			String errMsg = "Unable to open the sheet containing orders for which vendor is paid";
850 
			logger.error(errMsg, e);
851 
			addActionError(errMsg);
852 
			return;
5189 varun.gupt 853 
		} catch (IOException e) {
5386 phani.kuma 854 
			String errMsg = "IOException occured while reading the file containing orders for which vendor is paid";
855 
			logger.error(errMsg, e);
856 
			addActionError(errMsg);
857 
			return;
5098 varun.gupt 858 
		}
5189 varun.gupt 859 
 
5098 varun.gupt 860 
        TransactionClient tsc;
861 
        int countSuccessfulSaves = 0;
862 
 
5189 varun.gupt 863 
        try	{
864 
        	tsc = new TransactionClient();
865 
        	in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
866 
 
867 
	    	for (long orderId: orderIds)	{
868 
 
5198 varun.gupt 869 
	    		logger.info("Marking as paid, Order Id: " + orderId);
5189 varun.gupt 870 
	    		try {
5386 phani.kuma 871 
	    			transactionClient.updateOrderOnlyAsPaidToVendor(orderId);
5189 varun.gupt 872 
					countSuccessfulSaves += 1;
873 
 
874 
				} catch (TransactionServiceException e) {
875 
					logger.error("Transaction Service Exception while getting order for id, " + orderId, e);
876 
					addActionError("Transaction Service Exception while getting order for id, " + orderId);
877 
				}
878 
	    	}
5386 phani.kuma 879 
        } catch (Exception e) {
880 
			String errMsg = e.getMessage();
881 
			logger.error(errMsg, e);
882 
			addActionError(errMsg);
883 
		}
5098 varun.gupt 884 
        addActionMessage("Successfully marked " + countSuccessfulSaves + " orders as paid to vendor");
885 
	}
886 
 
5386 phani.kuma 887 
	public void uploadOrderIdsToBeMarkedReturned()	{
888 
		logger.info("Uploading Order Ids to be marked as Returned");
889 
		List<Long> orderIds = new ArrayList<Long>();
4875 varun.gupt 890 
 
5386 phani.kuma 891 
		try {
892 
			FileInputStream uploadedFile = new FileInputStream(this.orderIdsToBeMarked);
893 
			String uploadedContent = IOUtils.toString(uploadedFile);
894 
 
895 
			for(String s: uploadedContent.trim().split("\n"))	{
896 
 
897 
				if(! s.trim().isEmpty()) {
898 
					orderIds.add(Long.parseLong(s));
899 
				}
900 
			}
901 
		} catch (FileNotFoundException e) {
902 
			String errMsg = "Unable to open the sheet containing returned orders to be marked";
903 
			logger.error(errMsg, e);
904 
			addActionError(errMsg);
905 
			return;
906 
		} catch (IOException e) {
907 
			String errMsg = "IOException occured while reading the file containing returned orders to be marked";
908 
			logger.error(errMsg, e);
909 
			addActionError(errMsg);
910 
			return;
911 
		}
912 
 
913 
        TransactionClient tsc;
914 
        int countSuccessfulSaves = 0;
915 
 
916 
        try	{
917 
        	tsc = new TransactionClient();
918 
        	in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
919 
 
920 
        	for (long orderId: orderIds)	{
921 
 
922 
	    		logger.info("Marking as Returned, Order Id: " + orderId);
923 
	    		try {
924 
	    			transactionClient.processReturn(orderId);
925 
					countSuccessfulSaves += 1;
926 
 
927 
				} catch (TransactionServiceException e) {
928 
					String errMsg = "Transaction Service Exception while getting order for id, " + orderId;
929 
					logger.error(errMsg, e);
930 
					addActionError(errMsg);
931 
				}
932 
	    	}
933 
		} catch (Exception e) {
934 
			String errMsg = e.getMessage();
935 
			logger.error(errMsg, e);
936 
			addActionError(errMsg);
937 
		}
938 
        addActionMessage("Successfully marked " + countSuccessfulSaves + " orders as returned to vendor");
939 
	}
940 
 
5481 phani.kuma 941 
	private ByteArrayOutputStream getReconciliationReport(long vendorId, long fromTime, long toTime, boolean notall) {
5386 phani.kuma 942 
		ByteArrayOutputStream baosXLS = new ByteArrayOutputStream();
943 
 
944 
		try {
5481 phani.kuma 945 
 
4745 varun.gupt 946 
			TransactionClient tsc = new TransactionClient();
947 
			in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
5386 phani.kuma 948 
 
5481 phani.kuma 949 
			List<Order> unpaidOrders = transactionClient.getBilledOrders(vendorId, notall, fromTime, toTime);
5189 varun.gupt 950 
			logger.info("Unpaid Order count: " + unpaidOrders.size());
4905 varun.gupt 951 
 
5386 phani.kuma 952 
			List<ReturnOrder> returns = new ArrayList<ReturnOrder>();
4745 varun.gupt 953 
 
5386 phani.kuma 954 
			try {
5481 phani.kuma 955 
				returns = transactionClient.getAllReturnOrders(notall, fromTime, toTime);
5386 phani.kuma 956 
			} catch (TTransportException e) {
957 
				tsc = new TransactionClient();
958 
				transactionClient = tsc.getClient();
5481 phani.kuma 959 
				returns = transactionClient.getAllReturnOrders(notall, fromTime, toTime);
5386 phani.kuma 960 
			}
961 
 
962 
			Map<Long, ReturnOrder> mapReturnOrderIds = new HashMap<Long, ReturnOrder>();
963 
 
964 
			for (ReturnOrder returnOrder: returns)
965 
			{
966 
				mapReturnOrderIds.put(returnOrder.getOrderId(), returnOrder);
967 
			}
968 
 
969 
			List<Long> ReturnOrderIds = new ArrayList<Long>(mapReturnOrderIds.keySet());
970 
 
971 
			List<Order> listofreturnedOrders = new ArrayList<Order>();
972 
 
973 
			try {
974 
				listofreturnedOrders = transactionClient.getOrderListForVendor(ReturnOrderIds, vendorId);
975 
			} catch (TTransportException e) {
976 
				tsc = new TransactionClient();
977 
				transactionClient = tsc.getClient();
978 
				listofreturnedOrders = transactionClient.getOrderListForVendor(ReturnOrderIds, vendorId);
979 
			}
980 
 
981 
			for (Order order: listofreturnedOrders)
982 
			{
983 
				order.setDelivery_timestamp(mapReturnOrderIds.get(order.getId()).getCreatedAt());
5481 phani.kuma 984 
				order.setVendorPaid(mapReturnOrderIds.get(order.getId()).isProcessedStatus());
5386 phani.kuma 985 
				returnedOrders.add(order);
986 
			}
987 
 
988 
			Set<Long> OriginalOrderIds = new HashSet<Long>();
989 
			Map<Long, Order> mapUnpaidOrders = new HashMap<Long, Order>();
990 
 
991 
			for (Order order: unpaidOrders)
992 
			{
993 
				if(order.getOriginalOrderId() == 0)	{
994 
					OriginalOrderIds.add(order.getId());
995 
				}
996 
				else {
997 
					OriginalOrderIds.add(order.getOriginalOrderId());
998 
				}
999 
				mapUnpaidOrders.put(order.getId(), order);
1000 
			}
1001 
 
1002 
	    	List<Order> reshippedPayableOrders = new ArrayList<Order>();
1003 
	    	List<Order> originalPayableOrders_user = new ArrayList<Order>();
1004 
	    	List<Order> originalPayableOrders_nouser = new ArrayList<Order>();
1005 
 
1006 
	    	Order originalOrder = null;
1007 
			boolean originalOrderUnpaid = false;
1008 
			Order tempOrder = null;
1009 
			boolean tempOrderUnpaid = false;
1010 
			long nextOrderId = 0;
1011 
			String previous_invoice_number = "";
1012 
 
1013 
			for (long originalOrderId: OriginalOrderIds)
1014 
			{
1015 
				originalOrder = null;
1016 
				originalOrderUnpaid = false;
1017 
				tempOrder = null;
1018 
				tempOrderUnpaid = false;
1019 
				nextOrderId = originalOrderId;
1020 
				previous_invoice_number = "";
5189 varun.gupt 1021 
 
5386 phani.kuma 1022 
				while (true)
1023 
				{
1024 
					if(mapUnpaidOrders.containsKey(nextOrderId)) {
1025 
						tempOrder = mapUnpaidOrders.get(nextOrderId);
1026 
						tempOrderUnpaid = true;
1027 
					}
1028 
					else {
1029 
						try {
1030 
							tempOrder = transactionClient.getOrder(nextOrderId);
1031 
						} catch (TTransportException e) {
1032 
							tsc = new TransactionClient();
1033 
							transactionClient = tsc.getClient();
1034 
							tempOrder = transactionClient.getOrder(nextOrderId);
1035 
						}
1036 
						tempOrderUnpaid = false;
1037 
					}
5189 varun.gupt 1038 
 
5386 phani.kuma 1039 
					if(tempOrderUnpaid && nextOrderId != originalOrderId) {
1040 
						if(previous_invoice_number.equals(tempOrder.getInvoice_number())) {
1041 
							reshippedNonPayableOrders.add(tempOrder);
1042 
						}
1043 
						else {
1044 
							reshippedPayableOrders.add(tempOrder);
1045 
						}
1046 
					}
5189 varun.gupt 1047 
 
5386 phani.kuma 1048 
					if(tempOrderUnpaid && nextOrderId == originalOrderId) {
1049 
						originalOrder = tempOrder;
1050 
						originalOrderUnpaid = true;
1051 
					}
5189 varun.gupt 1052 
 
5386 phani.kuma 1053 
					nextOrderId = tempOrder.getNew_order_id();
1054 
					previous_invoice_number = tempOrder.getInvoice_number();
1055 
					if(nextOrderId == 0) {
1056 
						if(originalOrderUnpaid) {
1057 
							if(refundOrderStatuses.contains(tempOrder.getStatus())) {
1058 
								originalPayableOrders_nouser.add(originalOrder);
1059 
							}
1060 
							else {
1061 
								originalPayableOrders_user.add(originalOrder);
1062 
							}
1063 
						}
1064 
						break;
1065 
					}
5189 varun.gupt 1066 
				}
1067 
			}
1068 
 
5386 phani.kuma 1069 
	    	PaymentClient psc = new PaymentClient();
1070 
	    	in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
1071 
 
1072 
	    	PaymentSettlement pmtSettlement = null;
1073 
	    	Payment payment = null;
1074 
 
1075 
			for (Order UserpayRequiredOrder: originalPayableOrders_user)
1076 
			{
5189 varun.gupt 1077 
				try {
5386 phani.kuma 1078 
					pmtSettlement = null;
5189 varun.gupt 1079 
 
5386 phani.kuma 1080 
					if(UserpayRequiredOrder.isCod()) {
1081 
						try {
1082 
							pmtSettlement = transactionClient.getSettlementForCod(UserpayRequiredOrder.getId(), false);
1083 
						} catch (TTransportException e) {
1084 
							tsc = new TransactionClient();
1085 
							transactionClient = tsc.getClient();
1086 
							pmtSettlement = transactionClient.getSettlementForCod(UserpayRequiredOrder.getId(), false);
1087 
						}
5189 varun.gupt 1088 
 
5386 phani.kuma 1089 
						if(pmtSettlement.getReferenceId() > 0) {
1090 
							paymentSettledOrdersMap.put(UserpayRequiredOrder, pmtSettlement);
5189 varun.gupt 1091 
						}
5386 phani.kuma 1092 
						else {
1093 
							unsettledOrders.add(UserpayRequiredOrder);
1094 
						}
1095 
					}
1096 
					else {
1097 
						payment = null;
5189 varun.gupt 1098 
 
5386 phani.kuma 1099 
						try {
1100 
							payment = paymentClient.getSuccessfulPaymentForTxnId(UserpayRequiredOrder.getTransactionId());
1101 
						} catch (TTransportException e) {
1102 
							psc = new PaymentClient();
1103 
					    	paymentClient = psc.getClient();
1104 
					    	payment = paymentClient.getSuccessfulPaymentForTxnId(UserpayRequiredOrder.getTransactionId());
5189 varun.gupt 1105 
						}
1106 
 
5386 phani.kuma 1107 
						try {
1108 
							pmtSettlement = transactionClient.getSettlementForPrepaid(payment.getPaymentId(), false);
1109 
						} catch (TTransportException e) {
1110 
							tsc = new TransactionClient();
1111 
							transactionClient = tsc.getClient();
1112 
							pmtSettlement = transactionClient.getSettlementForPrepaid(payment.getPaymentId(), false);
5189 varun.gupt 1113 
						}
1114 
 
5386 phani.kuma 1115 
						if(pmtSettlement.getReferenceId() > 0) {
1116 
							paymentSettledOrdersMap.put(UserpayRequiredOrder, pmtSettlement);
5189 varun.gupt 1117 
						}
5386 phani.kuma 1118 
						else {
1119 
							unsettledOrders.add(UserpayRequiredOrder);
1120 
						}
5189 varun.gupt 1121 
					}
5386 phani.kuma 1122 
				} catch (Exception e) {
1123 
					ordersWithErrors.add(UserpayRequiredOrder);
1124 
					String errMsg = "error occured in getReconciliationReport during " + UserpayRequiredOrder.getId();
1125 
					logger.error(errMsg, e);
1126 
					addActionError(errMsg);
5189 varun.gupt 1127 
				}
1128 
			}
1129 
 
5386 phani.kuma 1130 
			userPaymentNotRequiredOrders.addAll(originalPayableOrders_nouser);
1131 
			logger.info("No User Payment Required Original Orders Count: " + originalPayableOrders_nouser.size());
1132 
			userPaymentNotRequiredOrders.addAll(reshippedPayableOrders);
1133 
			logger.info("Reshipped Payable Orders Count: " + reshippedPayableOrders.size());
1134 
			logger.info("Payment Settlement Count: " + paymentSettledOrdersMap.keySet().size());
1135 
			logger.info("Unsettled Orders Count: " + unsettledOrders.size());
1136 
			logger.info("User Payment Not Required Orders Count: " + userPaymentNotRequiredOrders.size());
1137 
			logger.info("Reshipped Orders Count: " + reshippedNonPayableOrders.size());
1138 
			logger.info("Returned Orders Count: " + returnedOrders.size());
1139 
 
1140 
		    Workbook wb = new HSSFWorkbook();
1141 
 
1142 
		    Sheet totalPayableOrderSheet = wb.createSheet("Total Payable Orders");
1143 
			Sheet settledOrderSheet = wb.createSheet("Settled Payable Orders");
1144 
			Sheet unsettledOrderSheet = wb.createSheet("Unsettled Payable Orders");
1145 
			Sheet returnedOrderSheet = wb.createSheet("Receivable Orders");
1146 
			Sheet reshippedOrderSheet = wb.createSheet("Reshipped Non Payable Orders");
1147 
			Sheet errorOrderSheet = wb.createSheet("Orders With Errors");
1148 
 
1149 
			populateSettledOrderSheet(totalPayableOrderSheet, true);
1150 
		    populateSettledOrderSheet(settledOrderSheet, false);
1151 
			populateUnsettledOrderSheet(unsettledOrderSheet);
1152 
			populateReturnedOrderSheet(returnedOrderSheet);
1153 
		    populateReshippedOrderSheet(reshippedOrderSheet);
1154 
		    populateErrorOrderSheet(errorOrderSheet);
1155 
 
1156 
			wb.write(baosXLS);
1157 
			baosXLS.close();
1158 
 
1159 
		} catch (Exception e) {
1160 
			String errMsg = e.getMessage();
4745 varun.gupt 1161 
			logger.error(errMsg, e);
1162 
			addActionError(errMsg);
1163 
		}
1164 
 
4600 varun.gupt 1165 
		return baosXLS;
1166 
	}
1167 
 
5386 phani.kuma 1168 
	private void populateErrorOrderSheet(Sheet sheet) {
1169 
		int rowCount = 0;
1170 
 
1171 
		Row headerRow = sheet.createRow(rowCount ++);
1172 
	    headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
1173 
	    headerRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue("Original Order Id");
1174 
	    headerRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue("Vendor Id");
1175 
	    headerRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue("Order Date");
1176 
	    headerRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue("Billing Number");
1177 
	    headerRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue("Billing Date");
1178 
	    headerRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue("Returned Date");
1179 
	    headerRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue("Brand");
1180 
	    headerRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue("Model Name");
1181 
	    headerRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue("Model Number");
1182 
	    headerRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue("Color");
1183 
	    headerRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue("Quantity");
1184 
	    headerRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue("Unit Transfer Price");
1185 
	    headerRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue("Unit Selling Price");
1186 
	    headerRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue("Total Transfer Price");
1187 
	    headerRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue("Total Selling Price");
1188 
	    headerRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue("Current Status");
5481 phani.kuma 1189 
	    headerRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue("Vendor Paid");
4600 varun.gupt 1190 
 
5386 phani.kuma 1191 
    	try	{
1192 
 
1193 
    		Row contentRow = null;
1194 
			LineItem lineItem = null;
1195 
 
1196 
			for (Order order: ordersWithErrors)
1197 
			{
1198 
				contentRow = sheet.createRow(rowCount ++);
1199 
				lineItem = order.getLineitems().get(0);
1200 
 
1201 
		        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
1202 
		        if(order.getOriginalOrderId() == 0)	{
1203 
		        	contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getId());
1204 
				}
1205 
				else {
1206 
					contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getOriginalOrderId());
1207 
				}
1208 
		        contentRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue(order.getVendorId());
1209 
		        contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
1210 
			    contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
1211 
			    contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
1212 
			    contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
1213 
			    contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
1214 
			    contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
1215 
			    contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
1216 
			    contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
1217 
			    contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
1218 
			    contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price());
1219 
			    contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
1220 
			    contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price() * lineItem.getQuantity());
1221 
			    contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(lineItem.getTotal_price());
1222 
			    contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatus().name());
5481 phani.kuma 1223 
			    contentRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue(order.isVendorPaid());
5386 phani.kuma 1224 
	    	}
1225 
	    } catch (Exception e) {
1226 
	    	String errMsg = e.getMessage();
1227 
			logger.error(errMsg, e);
1228 
			addActionError(errMsg);
1229 
		}
4600 varun.gupt 1230 
	}
1231 
 
5386 phani.kuma 1232 
	private void populateReturnedOrderSheet(Sheet sheet) {
1233 
		int rowCount = 0;
1234 
 
1235 
		Row headerRow = sheet.createRow(rowCount ++);
4875 varun.gupt 1236 
	    headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
5386 phani.kuma 1237 
	    headerRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue("Original Order Id");
1238 
	    headerRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue("Vendor Id");
4875 varun.gupt 1239 
	    headerRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue("Order Date");
1240 
	    headerRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue("Billing Number");
1241 
	    headerRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue("Billing Date");
5386 phani.kuma 1242 
	    headerRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue("Returned Date");
4875 varun.gupt 1243 
	    headerRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue("Brand");
1244 
	    headerRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue("Model Name");
1245 
	    headerRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue("Model Number");
1246 
	    headerRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue("Color");
1247 
	    headerRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue("Quantity");
1248 
	    headerRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue("Unit Transfer Price");
1249 
	    headerRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue("Unit Selling Price");
1250 
	    headerRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue("Total Transfer Price");
1251 
	    headerRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue("Total Selling Price");
1252 
	    headerRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue("Current Status");
5481 phani.kuma 1253 
	    headerRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue("Processed");
4875 varun.gupt 1254 
 
5386 phani.kuma 1255 
    	try	{
1256 
 
1257 
    		Row contentRow = null;
1258 
			LineItem lineItem = null;
1259 
 
1260 
			for (Order order: returnedOrders)
1261 
			{
1262 
				contentRow = sheet.createRow(rowCount ++);
1263 
				lineItem = order.getLineitems().get(0);
1264 
 
1265 
		        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
1266 
		        if(order.getOriginalOrderId() == 0)	{
1267 
		        	contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getId());
4875 varun.gupt 1268 
				}
5386 phani.kuma 1269 
				else {
1270 
					contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getOriginalOrderId());
1271 
				}
1272 
		        contentRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue(order.getVendorId());
1273 
		        contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
4875 varun.gupt 1274 
			    contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
1275 
			    contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
1276 
			    contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
1277 
			    contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
1278 
			    contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
1279 
			    contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
1280 
			    contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
1281 
			    contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
5386 phani.kuma 1282 
			    contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price());
4875 varun.gupt 1283 
			    contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
5386 phani.kuma 1284 
			    contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price() * lineItem.getQuantity());
1285 
			    contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(lineItem.getTotal_price());
1286 
			    contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatus().name());
5481 phani.kuma 1287 
			    contentRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue(order.isVendorPaid());
5386 phani.kuma 1288 
	    	}
1289 
	    } catch (Exception e) {
1290 
	    	String errMsg = e.getMessage();
1291 
			logger.error(errMsg, e);
1292 
			addActionError(errMsg);
4875 varun.gupt 1293 
		}
1294 
	}
1295 
 
5386 phani.kuma 1296 
	private void populateReshippedOrderSheet(Sheet sheet) {
1297 
		int rowCount = 0;
5020 varun.gupt 1298 
 
5189 varun.gupt 1299 
	    Row headerRow = sheet.createRow(rowCount ++);
1300 
	    headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
5386 phani.kuma 1301 
	    headerRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue("Original Order Id");
1302 
	    headerRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue("Vendor Id");
5189 varun.gupt 1303 
	    headerRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue("Order Date");
1304 
	    headerRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue("Billing Number");
1305 
	    headerRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue("Billing Date");
1306 
	    headerRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue("Delivery Date");
1307 
	    headerRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue("Brand");
1308 
	    headerRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue("Model Name");
1309 
	    headerRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue("Model Number");
1310 
	    headerRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue("Color");
1311 
	    headerRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue("Quantity");
1312 
	    headerRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue("Unit Transfer Price");
1313 
	    headerRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue("Unit Selling Price");
1314 
	    headerRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue("Total Transfer Price");
1315 
	    headerRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue("Total Selling Price");
1316 
	    headerRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue("Current Status");
5481 phani.kuma 1317 
	    headerRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue("Vendor Paid");
5189 varun.gupt 1318 
 
1319 
    	try	{
5386 phani.kuma 1320 
 
1321 
    		Row contentRow = null;
1322 
			LineItem lineItem = null;
1323 
 
1324 
			for (Order order: reshippedNonPayableOrders)
1325 
			{
1326 
				contentRow = sheet.createRow(rowCount ++);
1327 
				lineItem = order.getLineitems().get(0);
5189 varun.gupt 1328 
 
5386 phani.kuma 1329 
		        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
1330 
		        if(order.getOriginalOrderId() == 0)	{
1331 
		        	contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getId());
5189 varun.gupt 1332 
				}
5386 phani.kuma 1333 
				else {
1334 
					contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getOriginalOrderId());
1335 
				}
1336 
		        contentRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue(order.getVendorId());
5189 varun.gupt 1337 
		        contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
1338 
			    contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
1339 
			    contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
5386 phani.kuma 1340 
			    if(order.getDelivery_timestamp() != 0)	{
1341 
			    	contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
1342 
				}
5189 varun.gupt 1343 
			    contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
1344 
			    contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
1345 
			    contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
1346 
			    contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
1347 
			    contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
1348 
			    contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price());
1349 
			    contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
1350 
			    contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price() * lineItem.getQuantity());
1351 
			    contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(lineItem.getTotal_price());
1352 
			    contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatusDescription());
5481 phani.kuma 1353 
			    contentRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue(order.isVendorPaid());
5189 varun.gupt 1354 
	    	}
1355 
	    } catch (Exception e) {
5386 phani.kuma 1356 
	    	String errMsg = e.getMessage();
1357 
			logger.error(errMsg, e);
1358 
			addActionError(errMsg);
5189 varun.gupt 1359 
		}
1360 
	}
1361 
 
5386 phani.kuma 1362 
	private void populateUnsettledOrderSheet(Sheet sheet) {
1363 
		int rowCount = 0;
5189 varun.gupt 1364 
 
5020 varun.gupt 1365 
	    Row headerRow = sheet.createRow(rowCount ++);
4600 varun.gupt 1366 
	    headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
5386 phani.kuma 1367 
	    headerRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue("Original Order Id");
1368 
	    headerRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue("Vendor Id");
4600 varun.gupt 1369 
	    headerRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue("Order Date");
1370 
	    headerRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue("Billing Number");
1371 
	    headerRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue("Billing Date");
1372 
	    headerRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue("Delivery Date");
1373 
	    headerRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue("Brand");
1374 
	    headerRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue("Model Name");
1375 
	    headerRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue("Model Number");
1376 
	    headerRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue("Color");
1377 
	    headerRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue("Quantity");
1378 
	    headerRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue("Unit Transfer Price");
1379 
	    headerRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue("Unit Selling Price");
1380 
	    headerRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue("Total Transfer Price");
1381 
	    headerRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue("Total Selling Price");
1382 
	    headerRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue("Current Status");
5481 phani.kuma 1383 
	    headerRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue("Vendor Paid");
4600 varun.gupt 1384 
	    headerRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue("Payment Type");
4745 varun.gupt 1385 
	    headerRow.createCell(OrderReportColumn.PAYMENT_STATUS.getValue()).setCellValue("Payment Status");
4600 varun.gupt 1386 
	    headerRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue("Payment Id");
1387 
	    headerRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue("Coupon Code");
1388 
 
5386 phani.kuma 1389 
    	outputPaymentUnsettledOrdersRows(sheet, rowCount);
1390 
	}
1391 
 
1392 
	private int outputPaymentUnsettledOrdersRows(Sheet sheet, int rowCount) {
1393 
 
1394 
    	try	{
1395 
    		PaymentClient psc = new PaymentClient();
1396 
	    	in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
1397 
 
1398 
	    	TransactionClient tsc = new TransactionClient();
4600 varun.gupt 1399 
			in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
5189 varun.gupt 1400 
 
5386 phani.kuma 1401 
			Row contentRow = null;
1402 
			LineItem lineItem = null;
1403 
			Payment pmt = null;
1404 
			Transaction txn = null;
1405 
 
1406 
			for (Order order: unsettledOrders)
1407 
			{
1408 
				contentRow = sheet.createRow(rowCount ++);
1409 
				lineItem = order.getLineitems().get(0);
1410 
				pmt = null;
1411 
				txn = null;
1412 
 
1413 
				try	{
1414 
					if(order.isCod()) {
1415 
						try {
1416 
							pmt = paymentClient.getPaymentForTxnId(order.getTransactionId()).get(0);
1417 
						} catch (TTransportException e) {
1418 
							psc = new PaymentClient();
1419 
					    	paymentClient = psc.getClient();
1420 
					    	pmt = paymentClient.getPaymentForTxnId(order.getTransactionId()).get(0);
1421 
						}
1422 
 
1423 
						try {
1424 
							txn = transactionClient.getTransaction(order.getTransactionId());
1425 
						} catch (TTransportException e) {
1426 
							tsc = new TransactionClient();
1427 
							transactionClient = tsc.getClient();
1428 
							txn = transactionClient.getTransaction(order.getTransactionId());
1429 
						}
1430 
					}
1431 
					else {
1432 
						try {
1433 
							pmt = paymentClient.getSuccessfulPaymentForTxnId(order.getTransactionId());
1434 
						} catch (TTransportException e) {
1435 
							psc = new PaymentClient();
1436 
					    	paymentClient = psc.getClient();
1437 
					    	pmt = paymentClient.getSuccessfulPaymentForTxnId(order.getTransactionId());
1438 
						}
1439 
 
1440 
						try {
1441 
							txn = transactionClient.getTransaction(pmt.getMerchantTxnId());
1442 
						} catch (TTransportException e) {
1443 
							tsc = new TransactionClient();
1444 
							transactionClient = tsc.getClient();
1445 
							txn = transactionClient.getTransaction(pmt.getMerchantTxnId());
1446 
						}
1447 
					}
1448 
 
1449 
			        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
1450 
			        if(order.getOriginalOrderId() == 0)	{
1451 
			        	contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getId());
1452 
					}
1453 
					else {
1454 
						contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getOriginalOrderId());
1455 
					}
1456 
			        contentRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue(order.getVendorId());
1457 
			        contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
1458 
				    contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
1459 
				    contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
1460 
				    if(order.getDelivery_timestamp() != 0)	{
1461 
				    	contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
1462 
					}
1463 
				    contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
1464 
				    contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
1465 
				    contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
1466 
				    contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
1467 
				    contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
1468 
				    contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price());
1469 
				    contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
1470 
				    contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price() * lineItem.getQuantity());
1471 
				    contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(lineItem.getTotal_price());
1472 
				    contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatus().name());
5481 phani.kuma 1473 
				    contentRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue(order.isVendorPaid());
5386 phani.kuma 1474 
 
1475 
				    if(pmt.getGatewayId() == 4)	{
1476 
				    	contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue("COD-" + codProviders.get(order.getLogistics_provider_id()));
1477 
				    }
1478 
				    else {
1479 
				    	contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue(paymentGateways.get(pmt.getGatewayId()));
1480 
				    }
1481 
			    	contentRow.createCell(OrderReportColumn.PAYMENT_STATUS.getValue()).setCellValue(pmt.getStatus().name());
1482 
			    	contentRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue(pmt.getPaymentId());
1483 
			    	contentRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue(txn.getCoupon_code());
1484 
 
1485 
				} catch (Exception e) {
1486 
					contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
1487 
					String errMsg = "error occured in outputPaymentUnsettledOrdersRows during " + order.getId();
1488 
					logger.error(errMsg, e);
1489 
					addActionError(errMsg);
5020 varun.gupt 1490 
				}
4600 varun.gupt 1491 
	    	}
5386 phani.kuma 1492 
	    } catch (Exception e) {
1493 
	    	String errMsg = e.getMessage();
1494 
			logger.error(errMsg, e);
1495 
			addActionError(errMsg);
5020 varun.gupt 1496 
		}
5386 phani.kuma 1497 
 
1498 
	    return rowCount;
5020 varun.gupt 1499 
	}
1500 
 
5386 phani.kuma 1501 
	private void populateSettledOrderSheet(Sheet sheet, boolean total) {
1502 
		int rowCount = 0;
5020 varun.gupt 1503 
 
5386 phani.kuma 1504 
	    Row headerRow = sheet.createRow(rowCount ++);
1505 
	    headerRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue("Order Id");
1506 
	    headerRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue("Original Order Id");
1507 
	    headerRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue("Vendor Id");
1508 
	    headerRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue("Order Date");
1509 
	    headerRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue("Billing Number");
1510 
	    headerRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue("Billing Date");
1511 
	    headerRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue("Delivery Date");
1512 
	    headerRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue("Brand");
1513 
	    headerRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue("Model Name");
1514 
	    headerRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue("Model Number");
1515 
	    headerRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue("Color");
1516 
	    headerRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue("Quantity");
1517 
	    headerRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue("Unit Transfer Price");
1518 
	    headerRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue("Unit Selling Price");
1519 
	    headerRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue("Total Transfer Price");
1520 
	    headerRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue("Total Selling Price");
1521 
	    headerRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue("Current Status");
5481 phani.kuma 1522 
	    headerRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue("Vendor Paid");
5386 phani.kuma 1523 
	    headerRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue("Payment Type");
1524 
	    headerRow.createCell(OrderReportColumn.PAYMENT_STATUS.getValue()).setCellValue("Payment Status");
1525 
	    headerRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue("Payment Reference");
1526 
	    headerRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue("Coupon Code");
1527 
	    headerRow.createCell(OrderReportColumn.SETTLEMENT_DATE.getValue()).setCellValue("Settlement Date");
1528 
	    headerRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue("Service Tax");
1529 
	    headerRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue("Other Charges");
1530 
	    headerRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue("Net Collection");
1531 
	    headerRow.createCell(OrderReportColumn.USER_PAYABLE_AMOUNT.getValue()).setCellValue("User Payable Amount");
1532 
 
1533 
	    rowCount = outputPaymentSettledOrdersRows(sheet, rowCount);
1534 
	    rowCount = outputPaymentNotRequiredOrdersRows(sheet, rowCount);
1535 
 
1536 
	    if(total) {
1537 
	    	rowCount = outputPaymentUnsettledOrdersRows(sheet, rowCount);
1538 
	    }
1539 
	}
1540 
 
1541 
	private int outputPaymentSettledOrdersRows(Sheet sheet, int rowCount) {
5020 varun.gupt 1542 
 
5386 phani.kuma 1543 
		try	{
1544 
			PaymentClient psc = new PaymentClient();
1545 
	    	in.shop2020.payments.PaymentService.Client paymentClient = psc.getClient();
1546 
 
1547 
	    	TransactionClient tsc = new TransactionClient();
1548 
			in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
1549 
 
1550 
			Row contentRow = null;
1551 
			Order order = null;
1552 
			PaymentSettlement paymentSettlement = null;
1553 
			Payment pmt = null;
1554 
			Transaction txn = null;
1555 
			LineItem lineItem = null;
1556 
 
1557 
	    	for (Entry<Order, PaymentSettlement> entry : paymentSettledOrdersMap.entrySet())
1558 
	    	{
1559 
		    	contentRow = sheet.createRow(rowCount ++);
1560 
		    	order = entry.getKey();
1561 
		    	paymentSettlement = entry.getValue();
1562 
		    	pmt = null;
1563 
		    	txn = null;
5020 varun.gupt 1564 
 
5386 phani.kuma 1565 
				try {
1566 
					if(order.isCod()) {
1567 
						try {
1568 
							pmt = paymentClient.getPaymentForTxnId(order.getTransactionId()).get(0);
1569 
						} catch (TTransportException e) {
1570 
							psc = new PaymentClient();
1571 
					    	paymentClient = psc.getClient();
1572 
					    	pmt = paymentClient.getPaymentForTxnId(order.getTransactionId()).get(0);
1573 
						}
1574 
 
1575 
						try {
1576 
							txn = transactionClient.getTransaction(order.getTransactionId());
1577 
						} catch (TTransportException e) {
1578 
							tsc = new TransactionClient();
1579 
							transactionClient = tsc.getClient();
1580 
							txn = transactionClient.getTransaction(order.getTransactionId());
1581 
						}
1582 
					}
1583 
					else {
1584 
						try {
1585 
							pmt = paymentClient.getSuccessfulPaymentForTxnId(order.getTransactionId());
1586 
						} catch (TTransportException e) {
1587 
							psc = new PaymentClient();
1588 
					    	paymentClient = psc.getClient();
1589 
					    	pmt = paymentClient.getSuccessfulPaymentForTxnId(order.getTransactionId());
1590 
						}
1591 
 
1592 
						try {
1593 
							txn = transactionClient.getTransaction(pmt.getMerchantTxnId());
1594 
						} catch (TTransportException e) {
1595 
							tsc = new TransactionClient();
1596 
							transactionClient = tsc.getClient();
1597 
							txn = transactionClient.getTransaction(pmt.getMerchantTxnId());
1598 
						}
1599 
					}
1600 
			    	lineItem = order.getLineitems().get(0);
1601 
 
1602 
			        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
1603 
			        if(order.getOriginalOrderId() == 0)	{
1604 
			        	contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getId());
1605 
					}
1606 
					else {
1607 
						contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getOriginalOrderId());
1608 
					}
1609 
			        contentRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue(order.getVendorId());
1610 
			        contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
1611 
				    contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
1612 
				    contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
1613 
				    if(order.getDelivery_timestamp() != 0)	{
1614 
				    	contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
1615 
					}
1616 
				    contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
1617 
				    contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
1618 
				    contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
1619 
				    contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
1620 
				    contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
1621 
				    contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price());
1622 
				    contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
1623 
				    contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price() * lineItem.getQuantity());
1624 
				    contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(lineItem.getTotal_price());
1625 
				    contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatus().name());
5481 phani.kuma 1626 
				    contentRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue(order.isVendorPaid());
5386 phani.kuma 1627 
 
1628 
					if(pmt.getGatewayId() == 4)	{
1629 
				    	contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue("COD-" + codProviders.get(order.getLogistics_provider_id()));
1630 
				    	contentRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue(paymentSettlement.getServiceTax());
1631 
					    contentRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue(paymentSettlement.getOtherCharges());
1632 
					    contentRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue(paymentSettlement.getNetCollection());
1633 
					    contentRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue(paymentSettlement.getReferenceId());
1634 
				    }
1635 
				    else {
1636 
				    	contentRow.createCell(OrderReportColumn.PAYMENT_TYPE.getValue()).setCellValue(paymentGateways.get(pmt.getGatewayId()));
1637 
				    	contentRow.createCell(OrderReportColumn.SERVICE_TAX.getValue()).setCellValue((paymentSettlement.getServiceTax() * order.getTotal_amount()) / pmt.getAmount());
1638 
					    contentRow.createCell(OrderReportColumn.OTHER_CHARGES.getValue()).setCellValue((paymentSettlement.getOtherCharges() * order.getTotal_amount()) / pmt.getAmount());
1639 
					    contentRow.createCell(OrderReportColumn.NET_COLLECTION.getValue()).setCellValue((paymentSettlement.getNetCollection() * order.getTotal_amount()) / pmt.getAmount());
1640 
					    contentRow.createCell(OrderReportColumn.PAYMENT_ID.getValue()).setCellValue(pmt.getPaymentId());
1641 
				    }
1642 
			    	contentRow.createCell(OrderReportColumn.PAYMENT_STATUS.getValue()).setCellValue(pmt.getStatus().name());
1643 
			    	contentRow.createCell(OrderReportColumn.SETTLEMENT_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(paymentSettlement.getSettlementDate())));
1644 
				    contentRow.createCell(OrderReportColumn.USER_PAYABLE_AMOUNT.getValue()).setCellValue(order.getTotal_amount());
1645 
				    contentRow.createCell(OrderReportColumn.COUPON_CODE.getValue()).setCellValue(txn.getCoupon_code());
1646 
 
1647 
		    	} catch (Exception e) {
1648 
					contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
1649 
					String errMsg = "error occured in outputPaymentSettledOrdersRows during " + order.getId();
1650 
					logger.error(errMsg, e);
1651 
					addActionError(errMsg);
1652 
				}
4715 varun.gupt 1653 
			}
5386 phani.kuma 1654 
		} catch (Exception e) {
1655 
			String errMsg = e.getMessage();
1656 
			logger.error(errMsg, e);
1657 
			addActionError(errMsg);
5020 varun.gupt 1658 
		}
5386 phani.kuma 1659 
 
1660 
		return rowCount;
5020 varun.gupt 1661 
	}
5098 varun.gupt 1662 
 
5386 phani.kuma 1663 
	private int outputPaymentNotRequiredOrdersRows(Sheet sheet, int rowCount) {
5020 varun.gupt 1664 
 
5386 phani.kuma 1665 
		try	{
1666 
 
1667 
			Row contentRow = null;
1668 
			LineItem lineItem = null;
1669 
 
1670 
			for (Order order: userPaymentNotRequiredOrders)
1671 
			{
1672 
				contentRow = sheet.createRow(rowCount ++);
1673 
				lineItem = order.getLineitems().get(0);
5189 varun.gupt 1674 
 
5386 phani.kuma 1675 
		        contentRow.createCell(OrderReportColumn.ORDER_ID.getValue()).setCellValue(order.getId());
1676 
		        if(order.getOriginalOrderId() == 0)	{
1677 
		        	contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getId());
5189 varun.gupt 1678 
				}
5386 phani.kuma 1679 
				else {
1680 
					contentRow.createCell(OrderReportColumn.ORIGINAL_ORDER_ID.getValue()).setCellValue(order.getOriginalOrderId());
1681 
				}
1682 
		        contentRow.createCell(OrderReportColumn.VENDOR_ID.getValue()).setCellValue(order.getVendorId());
5020 varun.gupt 1683 
		        contentRow.createCell(OrderReportColumn.ORDER_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getCreated_timestamp())));
1684 
			    contentRow.createCell(OrderReportColumn.BILLING_NUMBER.getValue()).setCellValue(order.getInvoice_number());
1685 
			    contentRow.createCell(OrderReportColumn.BILLING_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getBilling_timestamp())));
5386 phani.kuma 1686 
			    if(order.getDelivery_timestamp() != 0)	{
1687 
			    	contentRow.createCell(OrderReportColumn.DELIVERY_DATE.getValue()).setCellValue(DATE_FORMAT.format(new Date(order.getDelivery_timestamp())));
1688 
				}
5020 varun.gupt 1689 
			    contentRow.createCell(OrderReportColumn.BRAND.getValue()).setCellValue(getValueForEmptyString(lineItem.getBrand()));
1690 
			    contentRow.createCell(OrderReportColumn.MODEL_NAME.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_name()));
1691 
			    contentRow.createCell(OrderReportColumn.MODEL_NUMBER.getValue()).setCellValue(getValueForEmptyString(lineItem.getModel_number()));
1692 
			    contentRow.createCell(OrderReportColumn.COLOR.getValue()).setCellValue(getValueForEmptyString(lineItem.getColor()));
1693 
			    contentRow.createCell(OrderReportColumn.QUANTITY.getValue()).setCellValue(lineItem.getQuantity());
1694 
			    contentRow.createCell(OrderReportColumn.UNIT_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price());
1695 
			    contentRow.createCell(OrderReportColumn.UNIT_SELLING_PRICE.getValue()).setCellValue(lineItem.getUnit_price());
1696 
			    contentRow.createCell(OrderReportColumn.TOTAL_TRANSFER_PRICE.getValue()).setCellValue(lineItem.getTransfer_price() * lineItem.getQuantity());
1697 
			    contentRow.createCell(OrderReportColumn.TOTAL_SELLING_PRICE.getValue()).setCellValue(lineItem.getTotal_price());
5386 phani.kuma 1698 
			    contentRow.createCell(OrderReportColumn.CURRENT_STATUS.getValue()).setCellValue(order.getStatus().name());
5481 phani.kuma 1699 
			    contentRow.createCell(OrderReportColumn.VENDOR_PAID.getValue()).setCellValue(order.isVendorPaid());
5386 phani.kuma 1700 
			    contentRow.createCell(OrderReportColumn.USER_PAYABLE_AMOUNT.getValue()).setCellValue(0);
5020 varun.gupt 1701 
			}
5386 phani.kuma 1702 
		} catch (Exception e) {
1703 
			String errMsg = e.getMessage();
1704 
			logger.error(errMsg, e);
1705 
			addActionError(errMsg);
4600 varun.gupt 1706 
		}
5020 varun.gupt 1707 
 
5386 phani.kuma 1708 
		return rowCount;
5020 varun.gupt 1709 
	}
1710 
 
5098 varun.gupt 1711 
	private void initiatializeDashboard()	{
1712 
		try	{
1713 
			OrderStatusGroups orderStatusGroups = new OrderStatusGroups();
1714 
			refundOrderStatuses = orderStatusGroups.getRefundedOrders();
1715 
 
5386 phani.kuma 1716 
			CatalogClient csc = new CatalogClient();
1717 
			in.shop2020.model.v1.catalog.InventoryService.Client catalogClient = csc.getClient();
5098 varun.gupt 1718 
			vendors = catalogClient.getAllVendors();
1719 
 
1720 
			TransactionClient tsc = new TransactionClient();
1721 
			in.shop2020.model.v1.order.TransactionService.Client transactionClient = tsc.getClient();
1722 
			ebsSettlementSummaries = transactionClient.getEBSSettlementSummaries();
1723 
 
5386 phani.kuma 1724 
		} catch (Exception e) {
1725 
			String errMsg = e.getMessage();
1726 
			logger.error(errMsg, e);
1727 
			addActionError(errMsg);
5098 varun.gupt 1728 
		}
1729 
	}
1730 
 
4600 varun.gupt 1731 
	public List<Vendor> getAllVendors()	{
1732 
		return this.vendors;
1733 
	}
1734 
 
1735 
	private String getValueForEmptyString(String s){
1736 
		if(s==null || s.equals(""))
1737 
			return "-";
1738 
		else
1739 
			return s;
1740 
	}
1741 
 
1742 
	private String getVendorName(long vendorId)	{
1743 
		for (Vendor vendor: vendors)	{
1744 
			if (vendor.getId() == vendorId)	return vendor.getName();
1745 
		}
1746 
		return null;
1747 
	}
1748 
 
5386 phani.kuma 1749 
	public File getHdfcSettlementReport() {
4600 varun.gupt 1750 
        return hdfcSettlementReport;
1751 
    }
1752 
 
1753 
    public void setHdfcSettlementReport(File hdfcSettlementReport) {
1754 
        this.hdfcSettlementReport = hdfcSettlementReport;
1755 
    }
1756 
 
1757 
    public File getEbsSettlementSummary()	{
1758 
    	return ebsSettlementSummary;
1759 
    }
1760 
 
1761 
    public void setEbsSettlementSummary(File ebsSettlementSummary)	{
1762 
    	this.ebsSettlementSummary = ebsSettlementSummary;
1763 
    }
1764 
 
1765 
    public File getEbsSettlementReport()	{
1766 
    	return ebsSettlementReport;
1767 
    }
1768 
 
1769 
    public void setEbsSettlementReport(File ebsSettlementReport)	{
1770 
    	this.ebsSettlementReport = ebsSettlementReport;
1771 
    }
1772 
 
1773 
    public String getBluedartSettlementDate()	{
1774 
    	return bluedartSettlementDate;
1775 
    }
1776 
 
1777 
    public void setBluedartSettlementDate(String bluedartSettlementDate)	{
1778 
    	this.bluedartSettlementDate = bluedartSettlementDate;
1779 
    }
1780 
 
1781 
    public File getBluedartSettlementReport()	{
1782 
    	return bluedartSettlementReport;
1783 
    }
1784 
 
1785 
    public void setBluedartSettlementReport(File bluedartSettlementReport)	{
1786 
    	this.bluedartSettlementReport = bluedartSettlementReport;
1787 
    }
1788 
 
4715 varun.gupt 1789 
    public String getAramexSettlementDate()	{
1790 
    	return this.aramexSettlementDate;
1791 
    }
1792 
 
1793 
    public void setAramexSettlementDate(String aramexSettlementDate)	{
1794 
    	this.aramexSettlementDate = aramexSettlementDate;
1795 
    }
1796 
 
1797 
    public void setAramexSettlementReport(File aramexSettlementReport) {
1798 
		this.aramexSettlementReport = aramexSettlementReport;
1799 
	}
1800 
 
1801 
	public File getAramexSettlementReport() {
1802 
		return aramexSettlementReport;
1803 
	}
1804 
 
5481 phani.kuma 1805 
	public String getDelhiverySettlementDate()	{
1806 
    	return this.delhiverySettlementDate;
1807 
    }
1808 
 
1809 
    public void setDelhiverySettlementDate(String delhiverySettlementDate)	{
1810 
    	this.delhiverySettlementDate = delhiverySettlementDate;
1811 
    }
1812 
 
1813 
    public void setDelhiverySettlementReport(File delhiverySettlementReport) {
1814 
		this.delhiverySettlementReport = delhiverySettlementReport;
1815 
	}
1816 
 
1817 
	public File getDelhiverySettlementReport() {
1818 
		return delhiverySettlementReport;
1819 
	}
1820 
 
4600 varun.gupt 1821 
	@Override
5386 phani.kuma 1822 
    public void setServletContext(ServletContext context) {
4600 varun.gupt 1823 
        this.context = context;
1824 
    }
1825 
 
1826 
    public String getServletContextPath() {
1827 
        return context.getContextPath();
1828 
    }
1829 
 
1830 
	@Override
1831 
	public void setServletResponse(HttpServletResponse response) {
1832 
		this.response = response;
1833 
	}
1834 
 
1835 
	@Override
1836 
	public void setServletRequest(HttpServletRequest request) {
1837 
		this.request = request;
1838 
	}
1839 
 
5386 phani.kuma 1840 
	public Map<Long, String> getEBSSettlementSummaries() {
4600 varun.gupt 1841 
		return ebsSettlementSummaries;
1842 
	}
5386 phani.kuma 1843 
 
1844 
	public void setOrderIdsToBeMarked(File orderIdsToBeMarked) {
1845 
		this.orderIdsToBeMarked = orderIdsToBeMarked;
5098 varun.gupt 1846 
	}
1847 
 
5386 phani.kuma 1848 
	public File getOrderIdsToBeMarked() {
1849 
		return orderIdsToBeMarked;
5098 varun.gupt 1850 
	}
4600 varun.gupt 1851 
}