Subversion Repositories SmartDukaan

Rev

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

Rev Author Line No. Line
1901 vikas 1 
package in.shop2020.support.controllers;
2 
 
2340 vikas 3 
import in.shop2020.model.v1.order.LineItem;
4 
import in.shop2020.model.v1.order.Order;
2980 vikas 5 
import in.shop2020.model.v1.order.OrderStatus;
1901 vikas 6 
import in.shop2020.model.v1.user.Affiliate;
7 
import in.shop2020.model.v1.user.MasterAffiliate;
8 
import in.shop2020.model.v1.user.TrackLog;
3378 vikas 9 
import in.shop2020.model.v1.user.TrackLogType;
3213 chandransh 10 
import in.shop2020.model.v1.user.UserAffiliateException;
1901 vikas 11 
import in.shop2020.model.v1.user.UserContextService.Client;
2340 vikas 12 
import in.shop2020.payments.Payment;
3293 vikas 13 
import in.shop2020.support.utils.ReportsUtils;
3125 rajveer 14 
import in.shop2020.thrift.clients.PaymentClient;
15 
import in.shop2020.thrift.clients.TransactionClient;
16 
import in.shop2020.thrift.clients.UserClient;
1901 vikas 17 
 
18 
import java.io.ByteArrayOutputStream;
19 
import java.io.IOException;
3293 vikas 20 
import java.text.DateFormat;
21 
import java.text.ParseException;
22 
import java.text.SimpleDateFormat;
2501 vikas 23 
import java.util.ArrayList;
3293 vikas 24 
import java.util.Calendar;
1901 vikas 25 
import java.util.Date;
26 
import java.util.HashMap;
2393 vikas 27 
import java.util.HashSet;
1901 vikas 28 
import java.util.List;
29 
import java.util.Map;
3315 vikas 30 
import java.util.Map.Entry;
2393 vikas 31 
import java.util.Set;
3315 vikas 32 
import java.util.SortedSet;
33 
import java.util.TimeZone;
34 
import java.util.TreeSet;
1901 vikas 35 
 
36 
import javax.servlet.ServletOutputStream;
37 
import javax.servlet.http.HttpServletRequest;
38 
import javax.servlet.http.HttpServletResponse;
3293 vikas 39 
import javax.servlet.http.HttpSession;
1901 vikas 40 
 
41 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
42 
import org.apache.poi.ss.usermodel.Cell;
43 
import org.apache.poi.ss.usermodel.CellStyle;
44 
import org.apache.poi.ss.usermodel.CreationHelper;
45 
import org.apache.poi.ss.usermodel.Font;
46 
import org.apache.poi.ss.usermodel.Row;
47 
import org.apache.poi.ss.usermodel.Sheet;
48 
import org.apache.poi.ss.usermodel.Workbook;
49 
import org.apache.poi.ss.util.CellRangeAddress;
3293 vikas 50 
import org.apache.struts2.convention.annotation.InterceptorRef;
51 
import org.apache.struts2.convention.annotation.InterceptorRefs;
3936 chandransh 52 
import org.apache.struts2.convention.annotation.Result;
53 
import org.apache.struts2.convention.annotation.Results;
1901 vikas 54 
import org.apache.struts2.interceptor.ServletRequestAware;
55 
import org.apache.struts2.interceptor.ServletResponseAware;
3213 chandransh 56 
import org.apache.thrift.TException;
57 
import org.slf4j.Logger;
58 
import org.slf4j.LoggerFactory;
1901 vikas 59 
 
2904 chandransh 60 
/**
61 
 * Provides reports about successful registrations and orders place through an
62 
 * affiliate.
63 
 *
64 
 * @author Vikas Malik
65 
 *
66 
 */
3293 vikas 67 
@InterceptorRefs({
68 
    @InterceptorRef("defaultStack"),
69 
    @InterceptorRef("login")
70 
})
3936 chandransh 71 
@Results({
72 
    @Result(name="authfail", type="redirectAction", params = {"actionName" , "reports"})
73 
})
3293 vikas 74 
public class AffiliateController implements ServletRequestAware, ServletResponseAware{
1901 vikas 75 
 
3293 vikas 76 
    private static Logger log = LoggerFactory.getLogger(AffiliateController.class);
3213 chandransh 77 
 
1901 vikas 78 
	private HttpServletResponse response;
79 
	private HttpServletRequest request;
3293 vikas 80 
	private HttpSession session;
81 
 
1901 vikas 82 
	private String errorMsg = "";
83 
	private List<MasterAffiliate> masterAffiliates;
3315 vikas 84 
	private Date startDate = null;
85 
	private Date endDate = null;
1901 vikas 86 
 
87 
	@Override
88 
	public void setServletResponse(HttpServletResponse res) {
89 
		this.response = res;
90 
	}
91 
 
92 
	@Override
93 
    public void setServletRequest(HttpServletRequest req) {
94 
	    this.request = req;
3293 vikas 95 
        this.session = req.getSession();
1901 vikas 96 
    }
97 
 
98 
	public String index()  {
3293 vikas 99 
	    log.info(request.getServletPath());
100 
        if (!ReportsUtils.canAccessReport(
101 
                (Long) session.getAttribute(ReportsUtils.ROLE),
102 
                request.getServletPath()))
103 
        {
3936 chandransh 104 
            return "authfail";
3293 vikas 105 
        }
1901 vikas 106 
        try {
3125 rajveer 107 
        	UserClient userContextServiceClient = new UserClient();
1901 vikas 108 
            Client userClient = userContextServiceClient.getClient();
109 
            masterAffiliates = userClient.getAllMasterAffiliates();
110 
        } catch (Exception e) {
3293 vikas 111 
            log.error("Error while getting all affiliates", e);
1901 vikas 112 
        }
113 
        return "report";
114 
    }
115 
 
116 
	public String create()	{
117 
	    try   {
118 
	        long mAfId = Long.parseLong(request.getParameter("masterAffiliate"));
3315 vikas 119 
	        String mAffName;
3293 vikas 120 
	        String startDateStr = request.getParameter("startDate");
121 
	        String endDateStr = request.getParameter("endDate");
122 
 
123 
	        DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
3315 vikas 124 
	        df.setTimeZone(TimeZone.getTimeZone("IST"));
3293 vikas 125 
	        try {
126 
	            startDate = df.parse(startDateStr);
127 
	            endDate = df.parse(endDateStr);
128 
	            Calendar cal = Calendar.getInstance();
129 
	            cal.setTime(endDate);
130 
	            endDate.setTime(cal.getTimeInMillis());
131 
	        } catch (ParseException pe) {
132 
	            errorMsg = "Please enter start and end dates in format MM/dd/yyyy";
133 
	            return "report";
134 
	        }
135 
 
3125 rajveer 136 
	        UserClient userContextServiceClient = new UserClient();
137 
            PaymentClient paymentServiceClient = new PaymentClient();
138 
            TransactionClient transactionServiceClient = new TransactionClient();
2340 vikas 139 
 
1901 vikas 140 
            Client userClient = userContextServiceClient.getClient();
2340 vikas 141 
            in.shop2020.payments.PaymentService.Client paymentClient = paymentServiceClient.getClient();
142 
 
2501 vikas 143 
            List<Affiliate> affiliates;
144 
            if (mAfId == -1) {
3315 vikas 145 
                mAffName = "All";
2501 vikas 146 
                affiliates = new ArrayList<Affiliate>();
147 
                for(MasterAffiliate mAffiliate : userClient.getAllMasterAffiliates()) {
148 
                    affiliates.addAll(userClient.getAffiliatesByMasterAffiliate(mAffiliate.getId()));
149 
                }
150 
            }
151 
            else {
152 
                affiliates = userClient.getAffiliatesByMasterAffiliate(mAfId);
3315 vikas 153 
                mAffName = userClient.getMasterAffiliateById(mAfId).getName();
2501 vikas 154 
            }
2980 vikas 155 
 
3315 vikas 156 
            SortedSet<Date> dates = new TreeSet<Date>();
157 
            Map<Date, Long> registerCountMap = new HashMap<Date, Long>();
158 
            Map<Date, Double> saleQuantityMap = new HashMap<Date, Double>();
159 
            Map<Date, Double> saleAmountMap = new HashMap<Date, Double>();
160 
 
161 
            Map<Date, Map<String, Double>> productQuantityMap = new HashMap<Date, Map<String, Double>>();
4657 anupam.sin 162 
            Map<Date, Map<Long, ArrayList<String>>> dateTxnProductMap = new HashMap<Date, Map<Long, ArrayList<String>>>();
3315 vikas 163 
            Map<Date, Map<String, Double>> productAmountMap = new HashMap<Date, Map<String,Double>>();
164 
 
2501 vikas 165 
            for (Affiliate aff : affiliates) {
3315 vikas 166 
                Set<Long> Payments = new HashSet<Long>(); // To deal with multiple refreshes on pay-success page.
3293 vikas 167 
                for (TrackLog tracklog : userClient.getTrackLogsByAffiliate(aff.getId(), startDate.getTime(), endDate.getTime())) {
3315 vikas 168 
                    Date date = df.parse(df.format(new Date(tracklog.getAddedOn())));
169 
                    dates.add(date);
3378 vikas 170 
                    if (tracklog.getEventType() == TrackLogType.NEW_REGISTRATION) {
3315 vikas 171 
                        if (registerCountMap.containsKey(date)) {
172 
                            Long count = registerCountMap.get(date);
173 
                            registerCountMap.put(date, ++count);
174 
                        }
175 
                        else {
176 
                            registerCountMap.put(date, 1l);
177 
                        }
178 
                    }
3378 vikas 179 
                    else if (tracklog.getEventType() == TrackLogType.PAYMENT_SUCCESS) {
3343 vikas 180 
                        long paymentId = Long.parseLong(tracklog.getData().replaceAll("\\(.*\\)", ""));
3315 vikas 181 
                        if (Payments.contains(paymentId)) {
182 
                            continue;
183 
                        }
184 
                        Payments.add(paymentId);
185 
                        Payment payment = paymentClient.getPayment(paymentId);
186 
                        List<Order> orders = transactionServiceClient.getClient().getOrdersForTransaction(
187 
                                        payment.getMerchantTxnId(),
188 
                                        payment.getUserId());
189 
                        for (Order order : orders) {
3341 vikas 190 
                            if (order.getStatus() == OrderStatus.DELIVERY_SUCCESS) {
3315 vikas 191 
                                List<LineItem> items = order.getLineitems();
192 
                                if (saleAmountMap.containsKey(date)) {
193 
                                    Double amount = saleAmountMap.get(date);
194 
                                    amount += order.getTotal_amount();
195 
                                    saleAmountMap.put(date, amount);
2980 vikas 196 
                                }
3315 vikas 197 
                                else {
198 
                                    saleAmountMap.put(date, order.getTotal_amount());
199 
                                }
2340 vikas 200 
                                for (LineItem item : items) {
3315 vikas 201 
                                    if (saleQuantityMap.containsKey(date)) {
202 
                                        Double quantity = saleQuantityMap.get(date);
203 
                                        quantity += item.getQuantity();
204 
                                        saleQuantityMap.put(date, quantity);
205 
                                    }
206 
                                    else {
207 
                                        saleQuantityMap.put(date, item.getQuantity());
208 
                                    }
209 
 
210 
                                    String productName = item.getBrand() + " "
2340 vikas 211 
                                            + item.getModel_name() + " "
3315 vikas 212 
                                            + item.getModel_number();
213 
                                    productName = productName.replace("null", "").replaceAll("  ", " ").trim();
4598 anupam.sin 214 
 
3315 vikas 215 
                                    if (productQuantityMap.containsKey(date)) {
216 
                                        Map<String, Double> pQuantityMap = productQuantityMap.get(date);
217 
                                        if (pQuantityMap.containsKey(productName)) {
218 
                                            Double quantity = pQuantityMap.get(productName);
219 
                                            quantity += item.getQuantity();
220 
                                            pQuantityMap.put(productName, quantity);
221 
                                        }
222 
                                        else {
223 
                                            pQuantityMap.put(productName, item.getQuantity());
224 
                                        }
225 
                                    }
226 
                                    else {
227 
                                        Map<String, Double> pQuantityMap = new HashMap<String, Double>();
228 
                                        pQuantityMap.put(productName, item.getQuantity());
229 
                                        productQuantityMap.put(date, pQuantityMap);
230 
                                    }
231 
 
4598 anupam.sin 232 
                                    if (dateTxnProductMap.containsKey(date)) {
4657 anupam.sin 233 
                                        Map<Long, ArrayList<String>> txnProductMap = dateTxnProductMap.get(date);
234 
                                        if (txnProductMap.containsKey(order.getTransactionId())) {
235 
                                            ArrayList<String> productList = txnProductMap.get(order.getTransactionId());
236 
                                            productList.add(productName);
237 
                                        } else {
238 
                                            ArrayList<String> productList = new ArrayList<String>();
239 
                                            productList.add(productName);
240 
                                            txnProductMap.put(order.getTransactionId(), productList);
241 
                                        }
4598 anupam.sin 242 
                                    }
243 
                                    else {
4657 anupam.sin 244 
                                        Map<Long, ArrayList<String>> txnProductMap = new HashMap<Long, ArrayList<String>>();
245 
                                        ArrayList<String> productList = new ArrayList<String>();
246 
                                        productList.add(productName);
247 
                                        txnProductMap.put(order.getTransactionId(), productList);
4598 anupam.sin 248 
                                        dateTxnProductMap.put(date, txnProductMap);
249 
                                    }
250 
 
3315 vikas 251 
                                    if (productAmountMap.containsKey(date)) {
252 
                                        Map<String, Double> pAmountMap = productAmountMap.get(date);
253 
                                        if (pAmountMap.containsKey(productName)) {
254 
                                            Double amount = pAmountMap.get(productName);
255 
                                            amount += item.getTotal_price();
256 
                                            pAmountMap.put(productName, amount);
257 
                                        }
258 
                                        else {
259 
                                            pAmountMap.put(productName, item.getTotal_price());
260 
                                        }
261 
                                    }
262 
                                    else {
263 
                                        Map<String, Double> pAmountMap = new HashMap<String, Double>();
264 
                                        pAmountMap.put(productName, item.getTotal_price());
265 
                                        productAmountMap.put(date, pAmountMap);
266 
                                    }
2340 vikas 267 
                                }
268 
                            }
269 
                        }
270 
                    }
1901 vikas 271 
                }
272 
            }
3315 vikas 273 
 
1901 vikas 274 
            // Preparing XLS file for output
275 
            response.setContentType("application/vnd.ms-excel");
276 
 
3315 vikas 277 
            response.setHeader("Content-disposition", "inline; filename=" + mAffName + "-affiliate-report" + ".xls");
1901 vikas 278 
 
279 
            ServletOutputStream sos;
280 
            try {
4598 anupam.sin 281 
                ByteArrayOutputStream baos = getSpreadSheetData(mAffName,
282 
                                                                   dates,
283 
                                                                   registerCountMap,
284 
                                                                   saleQuantityMap,
285 
                                                                   saleAmountMap,
286 
                                                                   productQuantityMap,
287 
                                                                   dateTxnProductMap,
288 
                                                                   productAmountMap);
1901 vikas 289 
                sos = response.getOutputStream();
290 
                baos.writeTo(sos);
291 
                sos.flush();
292 
            } catch (IOException e) {
3293 vikas 293 
                log.error("Unable to stream the affiliates report", e);
1901 vikas 294 
                errorMsg = "Failed to write to response.";
295 
            }
3213 chandransh 296 
        } catch (UserAffiliateException e) {
3293 vikas 297 
            log.error("Error while getting affiliated users", e);
1901 vikas 298 
            errorMsg = e.getMessage();
3213 chandransh 299 
        } catch (TException e) {
3293 vikas 300 
            log.error("Unable to get affiliated users", e);
3213 chandransh 301 
            errorMsg = e.getMessage();
302 
        } catch (Exception e) {
3293 vikas 303 
            log.error("Unexpected exception", e);
3213 chandransh 304 
            errorMsg = e.getMessage();
1901 vikas 305 
        }
306 
        return null;
307 
	}
308 
 
3315 vikas 309 
    // Prepares the XLS worksheet object and fills in the data with proper formatting
310 
	private ByteArrayOutputStream getSpreadSheetData(String mAffName,
311 
	        SortedSet<Date> dates,
312 
            Map<Date, Long> registerCountMap,
313 
            Map<Date, Double> saleQuantityMap,
4598 anupam.sin 314 
            Map<Date, Double> saleAmountMap,
3315 vikas 315 
            Map<Date, Map<String, Double>> productQuantityMap,
4657 anupam.sin 316 
            Map<Date, Map<Long, ArrayList<String>>> dateTxnProductMap,
3315 vikas 317 
            Map<Date, Map<String, Double>> productAmountMap)
1901 vikas 318 
	{
319 
	    ByteArrayOutputStream baosXLS = new ByteArrayOutputStream();
320 
 
321 
		Workbook wb = new HSSFWorkbook();
322 
 
323 
	    Font font = wb.createFont();
324 
	    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
325 
	    CellStyle style = wb.createCellStyle();
326 
	    style.setFont(font);
327 
 
328 
	    CreationHelper createHelper = wb.getCreationHelper();
329 
	    CellStyle dateCellStyle = wb.createCellStyle();
3315 vikas 330 
	    dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("DD/MM/YYYY"));
1901 vikas 331 
 
3315 vikas 332 
	    createSummarySheet(mAffName, dates, registerCountMap, saleQuantityMap, saleAmountMap, wb, style, dateCellStyle);
4598 anupam.sin 333 
	    createSaleDetailSheet(mAffName, dates, dateTxnProductMap, productQuantityMap, productAmountMap, wb, style, dateCellStyle);
1901 vikas 334 
 
335 
		// Write the workbook to the output stream
336 
		try {
337 
			wb.write(baosXLS);
338 
			baosXLS.close();
339 
		} catch (IOException e) {
3293 vikas 340 
			log.error("Unable to get the byte array for the affiliate report", e);
1901 vikas 341 
		}
342 
		return baosXLS;
343 
	}
344 
 
3315 vikas 345 
    private void createSaleDetailSheet(String mAffName, SortedSet<Date> dates,
4657 anupam.sin 346 
            Map<Date, Map<Long, ArrayList<String>>> dateTxnProductMap,
3315 vikas 347 
            Map<Date, Map<String, Double>> productQuantityMap,
348 
            Map<Date, Map<String, Double>> productAmountMap, Workbook wb,
349 
            CellStyle style, CellStyle dateCellStyle) {
350 
        // Product Sales SHEET
351 
        Sheet affSheet = wb.createSheet("Product Sales Report");
352 
        short affSerialNo = 0;
353 
 
354 
        DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
355 
 
356 
        Row affTitleRow = affSheet.createRow(affSerialNo ++);
357 
        Cell affTitleCell = affTitleRow.createCell(0);
358 
        affTitleCell.setCellValue("Modelwise Sales Report");
359 
        affTitleCell.setCellStyle(style);
360 
        affSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
361 
 
362 
        affSheet.createRow(affSerialNo ++);
363 
        Row affNameRow = affSheet.createRow(affSerialNo ++);
364 
        Cell affNameCell = affNameRow.createCell(0);
365 
        affNameCell.setCellValue("Affiliate : " + mAffName);
366 
        affNameCell.setCellStyle(style);
367 
        affSheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 6));
368 
 
369 
        Row affDateRangeRow = affSheet.createRow(affSerialNo ++);
370 
        Cell affDateRangeCell = affDateRangeRow.createCell(0);
371 
        affDateRangeCell.setCellValue("Date Range : " + df.format(startDate) + " - " + df.format(endDate));
372 
        affDateRangeCell.setCellStyle(style);
373 
        affSheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 6));
374 
 
375 
        affSheet.createRow(affSerialNo ++);affSheet.createRow(affSerialNo ++);
376 
 
377 
        Row affHeaderRow = affSheet.createRow(affSerialNo++);
378 
        affHeaderRow.createCell(0).setCellValue("Date");
379 
        affHeaderRow.createCell(1).setCellValue("ProductName");
4598 anupam.sin 380 
        affHeaderRow.createCell(2).setCellValue("Transaction Id");
3315 vikas 381 
        affHeaderRow.createCell(3).setCellValue("Amount");
382 
        for (int i=0; i<4 ;i++) {
383 
            affHeaderRow.getCell(i).setCellStyle(style);
384 
        }
385 
 
3343 vikas 386 
        Double totalQuantity = 0d;
387 
        Double totalAmount = 0d;
3315 vikas 388 
        for(Date date : dates) {
4598 anupam.sin 389 
            if (!dateTxnProductMap.containsKey(date)) {
3315 vikas 390 
                continue;
391 
            }
4598 anupam.sin 392 
            //HashSet<String> tmpProductSet = new HashSet<String>();
4657 anupam.sin 393 
            ArrayList<String> prodList = new ArrayList<String>();
4598 anupam.sin 394 
            Row commContentRow = null;
4657 anupam.sin 395 
            for (Entry<Long, ArrayList<String>> txnEntry : dateTxnProductMap.get(date).entrySet()) {
396 
                prodList = txnEntry.getValue();
4598 anupam.sin 397 
                Long txn = txnEntry.getKey();
4657 anupam.sin 398 
                for (String prodName : prodList) {
4674 anupam.sin 399 
                    affSerialNo++;
4657 anupam.sin 400 
                    commContentRow = affSheet.createRow(affSerialNo);
401 
                    commContentRow.createCell(0).setCellValue(date);
402 
                    commContentRow.getCell(0).setCellStyle(dateCellStyle);
403 
                    commContentRow.createCell(1).setCellValue(prodName);
404 
                    commContentRow.createCell(2).setCellValue(txn);
405 
                    Double amount = productAmountMap.get(date).get(prodName);
406 
                    amount = amount/(productQuantityMap.get(date).get(prodName));
407 
                    totalAmount += amount;
408 
                    commContentRow.createCell(3).setCellValue(amount);
409 
                    totalQuantity++;
410 
                }
3315 vikas 411 
            }
412 
        }
3343 vikas 413 
        affSerialNo+=2;
414 
        Row commContentRow = affSheet.createRow(affSerialNo);
415 
        commContentRow.createCell(0).setCellValue("Total");
416 
        commContentRow.createCell(1).setCellValue("");
417 
        commContentRow.createCell(2).setCellValue(totalQuantity);
418 
        commContentRow.createCell(3).setCellValue(totalAmount);
419 
 
420 
        for (int i = 0; i<4; i++) {
421 
            affSheet.autoSizeColumn(i);
422 
        }
3315 vikas 423 
    }
424 
 
425 
    private void createSummarySheet(String mAffName,
426 
            SortedSet<Date> dates,
427 
            Map<Date, Long> registerCountMap,
428 
            Map<Date, Double> saleQuantityMap,
429 
            Map<Date, Double> saleAmountMap,
430 
            Workbook wb,
431 
            CellStyle style,
432 
            CellStyle dateCellStyle)
433 
    {
434 
		// Summary SHEET
435 
	    Sheet affSheet = wb.createSheet("Summary Report");
1901 vikas 436 
	    short affSerialNo = 0;
3315 vikas 437 
 
438 
	    DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
1901 vikas 439 
 
440 
	    Row affTitleRow = affSheet.createRow(affSerialNo ++);
441 
	    Cell affTitleCell = affTitleRow.createCell(0);
3315 vikas 442 
	    affTitleCell.setCellValue("Daily Stats Report");
1901 vikas 443 
	    affTitleCell.setCellStyle(style);
444 
	    affSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
445 
 
446 
	    affSheet.createRow(affSerialNo ++);
3315 vikas 447 
	    Row affNameRow = affSheet.createRow(affSerialNo ++);
448 
        Cell affNameCell = affNameRow.createCell(0);
449 
        affNameCell.setCellValue("Affiliate : " + mAffName);
450 
        affNameCell.setCellStyle(style);
451 
        affSheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 6));
452 
 
453 
        Row affDateRangeRow = affSheet.createRow(affSerialNo ++);
454 
        Cell affDateRangeCell = affDateRangeRow.createCell(0);
455 
        affDateRangeCell.setCellValue("Date Range : " + df.format(startDate) + " - " + df.format(endDate));
456 
        affDateRangeCell.setCellStyle(style);
457 
        affSheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 6));
458 
 
459 
	    affSheet.createRow(affSerialNo ++);affSheet.createRow(affSerialNo ++);
1901 vikas 460 
 
461 
	    Row affHeaderRow = affSheet.createRow(affSerialNo++);
3315 vikas 462 
	    affHeaderRow.createCell(0).setCellValue("Date");
463 
	    affHeaderRow.createCell(1).setCellValue("New Registrations");
464 
	    affHeaderRow.createCell(2).setCellValue("Sales Count");
465 
	    affHeaderRow.createCell(3).setCellValue("Amount");
466 
	    for (int i=0; i<4 ;i++) {
1901 vikas 467 
	        affHeaderRow.getCell(i).setCellStyle(style);
468 
	    }
469 
 
3343 vikas 470 
	    Double totalregisterCount = 0d;
471 
        Double totalQuantity = 0d;
472 
	    Double totalAmount = 0d;
473 
        for(Date date : dates) {
3315 vikas 474 
		    affSerialNo++;
1901 vikas 475 
			Row commContentRow = affSheet.createRow(affSerialNo);
3315 vikas 476 
			Long registerCount = registerCountMap.get(date);
477 
			Double saleQuantity = saleQuantityMap.get(date);
478 
			Double saleAmount = saleAmountMap.get(date);
479 
			if (registerCount == null) {
480 
			    registerCount = 0l;
2340 vikas 481 
			}
3315 vikas 482 
			if (saleQuantity == null) {
483 
                saleQuantity = 0d;
484 
            }
485 
			if (saleAmount == null) {
486 
                saleAmount = 0d;
487 
            }
3343 vikas 488 
			totalregisterCount += registerCount;
489 
            totalQuantity += saleQuantity;
490 
			totalAmount += saleAmount;
491 
            commContentRow.createCell(0).setCellValue(date);
3315 vikas 492 
			commContentRow.getCell(0).setCellStyle(dateCellStyle);
493 
            commContentRow.createCell(1).setCellValue(registerCount);
494 
			commContentRow.createCell(2).setCellValue(saleQuantity);
495 
			commContentRow.createCell(3).setCellValue(saleAmount);
1901 vikas 496 
		}
3343 vikas 497 
        affSerialNo+=2;
498 
        Row commContentRow = affSheet.createRow(affSerialNo);
499 
        commContentRow.createCell(0).setCellValue("Total");
500 
        commContentRow.createCell(1).setCellValue(totalregisterCount);
501 
        commContentRow.createCell(2).setCellValue(totalQuantity);
502 
        commContentRow.createCell(3).setCellValue(totalAmount);
503 
 
3315 vikas 504 
        for (int i = 0; i<4; i++) {
1901 vikas 505 
            affSheet.autoSizeColumn(i);
506 
        }
507 
	}
508 
 
509 
	public String getErrorMsg() {
510 
		return errorMsg;
511 
	}
512 
 
513 
	public List<MasterAffiliate> getMasterAffiliates() {
514 
        return masterAffiliates;
515 
    }
2340 vikas 516 
}