Subversion Repositories SmartDukaan

Rev

Rev 4674 | 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);
6433 anupam.sin 186 
                        List<Order> orders = null;
187 
                        if(payment.isIsDigital() == false) {
188 
                            orders = transactionServiceClient.getClient().getOrdersForTransaction(
189 
                                    payment.getMerchantTxnId(),
190 
                                    payment.getUserId());
191 
                        } else {
192 
                            continue;
193 
                        }
3315 vikas 194 
                        for (Order order : orders) {
3341 vikas 195 
                            if (order.getStatus() == OrderStatus.DELIVERY_SUCCESS) {
3315 vikas 196 
                                List<LineItem> items = order.getLineitems();
197 
                                if (saleAmountMap.containsKey(date)) {
198 
                                    Double amount = saleAmountMap.get(date);
199 
                                    amount += order.getTotal_amount();
200 
                                    saleAmountMap.put(date, amount);
2980 vikas 201 
                                }
3315 vikas 202 
                                else {
203 
                                    saleAmountMap.put(date, order.getTotal_amount());
204 
                                }
2340 vikas 205 
                                for (LineItem item : items) {
3315 vikas 206 
                                    if (saleQuantityMap.containsKey(date)) {
207 
                                        Double quantity = saleQuantityMap.get(date);
208 
                                        quantity += item.getQuantity();
209 
                                        saleQuantityMap.put(date, quantity);
210 
                                    }
211 
                                    else {
212 
                                        saleQuantityMap.put(date, item.getQuantity());
213 
                                    }
214 
 
215 
                                    String productName = item.getBrand() + " "
2340 vikas 216 
                                            + item.getModel_name() + " "
3315 vikas 217 
                                            + item.getModel_number();
218 
                                    productName = productName.replace("null", "").replaceAll("  ", " ").trim();
4598 anupam.sin 219 
 
3315 vikas 220 
                                    if (productQuantityMap.containsKey(date)) {
221 
                                        Map<String, Double> pQuantityMap = productQuantityMap.get(date);
222 
                                        if (pQuantityMap.containsKey(productName)) {
223 
                                            Double quantity = pQuantityMap.get(productName);
224 
                                            quantity += item.getQuantity();
225 
                                            pQuantityMap.put(productName, quantity);
226 
                                        }
227 
                                        else {
228 
                                            pQuantityMap.put(productName, item.getQuantity());
229 
                                        }
230 
                                    }
231 
                                    else {
232 
                                        Map<String, Double> pQuantityMap = new HashMap<String, Double>();
233 
                                        pQuantityMap.put(productName, item.getQuantity());
234 
                                        productQuantityMap.put(date, pQuantityMap);
235 
                                    }
236 
 
4598 anupam.sin 237 
                                    if (dateTxnProductMap.containsKey(date)) {
4657 anupam.sin 238 
                                        Map<Long, ArrayList<String>> txnProductMap = dateTxnProductMap.get(date);
239 
                                        if (txnProductMap.containsKey(order.getTransactionId())) {
240 
                                            ArrayList<String> productList = txnProductMap.get(order.getTransactionId());
241 
                                            productList.add(productName);
242 
                                        } else {
243 
                                            ArrayList<String> productList = new ArrayList<String>();
244 
                                            productList.add(productName);
245 
                                            txnProductMap.put(order.getTransactionId(), productList);
246 
                                        }
4598 anupam.sin 247 
                                    }
248 
                                    else {
4657 anupam.sin 249 
                                        Map<Long, ArrayList<String>> txnProductMap = new HashMap<Long, ArrayList<String>>();
250 
                                        ArrayList<String> productList = new ArrayList<String>();
251 
                                        productList.add(productName);
252 
                                        txnProductMap.put(order.getTransactionId(), productList);
4598 anupam.sin 253 
                                        dateTxnProductMap.put(date, txnProductMap);
254 
                                    }
255 
 
3315 vikas 256 
                                    if (productAmountMap.containsKey(date)) {
257 
                                        Map<String, Double> pAmountMap = productAmountMap.get(date);
258 
                                        if (pAmountMap.containsKey(productName)) {
259 
                                            Double amount = pAmountMap.get(productName);
260 
                                            amount += item.getTotal_price();
261 
                                            pAmountMap.put(productName, amount);
262 
                                        }
263 
                                        else {
264 
                                            pAmountMap.put(productName, item.getTotal_price());
265 
                                        }
266 
                                    }
267 
                                    else {
268 
                                        Map<String, Double> pAmountMap = new HashMap<String, Double>();
269 
                                        pAmountMap.put(productName, item.getTotal_price());
270 
                                        productAmountMap.put(date, pAmountMap);
271 
                                    }
2340 vikas 272 
                                }
273 
                            }
274 
                        }
275 
                    }
1901 vikas 276 
                }
277 
            }
3315 vikas 278 
 
1901 vikas 279 
            // Preparing XLS file for output
280 
            response.setContentType("application/vnd.ms-excel");
281 
 
3315 vikas 282 
            response.setHeader("Content-disposition", "inline; filename=" + mAffName + "-affiliate-report" + ".xls");
1901 vikas 283 
 
284 
            ServletOutputStream sos;
285 
            try {
4598 anupam.sin 286 
                ByteArrayOutputStream baos = getSpreadSheetData(mAffName,
287 
                                                                   dates,
288 
                                                                   registerCountMap,
289 
                                                                   saleQuantityMap,
290 
                                                                   saleAmountMap,
291 
                                                                   productQuantityMap,
292 
                                                                   dateTxnProductMap,
293 
                                                                   productAmountMap);
1901 vikas 294 
                sos = response.getOutputStream();
295 
                baos.writeTo(sos);
296 
                sos.flush();
297 
            } catch (IOException e) {
3293 vikas 298 
                log.error("Unable to stream the affiliates report", e);
1901 vikas 299 
                errorMsg = "Failed to write to response.";
300 
            }
3213 chandransh 301 
        } catch (UserAffiliateException e) {
3293 vikas 302 
            log.error("Error while getting affiliated users", e);
1901 vikas 303 
            errorMsg = e.getMessage();
3213 chandransh 304 
        } catch (TException e) {
3293 vikas 305 
            log.error("Unable to get affiliated users", e);
3213 chandransh 306 
            errorMsg = e.getMessage();
307 
        } catch (Exception e) {
3293 vikas 308 
            log.error("Unexpected exception", e);
3213 chandransh 309 
            errorMsg = e.getMessage();
1901 vikas 310 
        }
311 
        return null;
312 
	}
313 
 
3315 vikas 314 
    // Prepares the XLS worksheet object and fills in the data with proper formatting
315 
	private ByteArrayOutputStream getSpreadSheetData(String mAffName,
316 
	        SortedSet<Date> dates,
317 
            Map<Date, Long> registerCountMap,
318 
            Map<Date, Double> saleQuantityMap,
4598 anupam.sin 319 
            Map<Date, Double> saleAmountMap,
3315 vikas 320 
            Map<Date, Map<String, Double>> productQuantityMap,
4657 anupam.sin 321 
            Map<Date, Map<Long, ArrayList<String>>> dateTxnProductMap,
3315 vikas 322 
            Map<Date, Map<String, Double>> productAmountMap)
1901 vikas 323 
	{
324 
	    ByteArrayOutputStream baosXLS = new ByteArrayOutputStream();
325 
 
326 
		Workbook wb = new HSSFWorkbook();
327 
 
328 
	    Font font = wb.createFont();
329 
	    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
330 
	    CellStyle style = wb.createCellStyle();
331 
	    style.setFont(font);
332 
 
333 
	    CreationHelper createHelper = wb.getCreationHelper();
334 
	    CellStyle dateCellStyle = wb.createCellStyle();
3315 vikas 335 
	    dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("DD/MM/YYYY"));
1901 vikas 336 
 
3315 vikas 337 
	    createSummarySheet(mAffName, dates, registerCountMap, saleQuantityMap, saleAmountMap, wb, style, dateCellStyle);
4598 anupam.sin 338 
	    createSaleDetailSheet(mAffName, dates, dateTxnProductMap, productQuantityMap, productAmountMap, wb, style, dateCellStyle);
1901 vikas 339 
 
340 
		// Write the workbook to the output stream
341 
		try {
342 
			wb.write(baosXLS);
343 
			baosXLS.close();
344 
		} catch (IOException e) {
3293 vikas 345 
			log.error("Unable to get the byte array for the affiliate report", e);
1901 vikas 346 
		}
347 
		return baosXLS;
348 
	}
349 
 
3315 vikas 350 
    private void createSaleDetailSheet(String mAffName, SortedSet<Date> dates,
4657 anupam.sin 351 
            Map<Date, Map<Long, ArrayList<String>>> dateTxnProductMap,
3315 vikas 352 
            Map<Date, Map<String, Double>> productQuantityMap,
353 
            Map<Date, Map<String, Double>> productAmountMap, Workbook wb,
354 
            CellStyle style, CellStyle dateCellStyle) {
355 
        // Product Sales SHEET
356 
        Sheet affSheet = wb.createSheet("Product Sales Report");
357 
        short affSerialNo = 0;
358 
 
359 
        DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
360 
 
361 
        Row affTitleRow = affSheet.createRow(affSerialNo ++);
362 
        Cell affTitleCell = affTitleRow.createCell(0);
363 
        affTitleCell.setCellValue("Modelwise Sales Report");
364 
        affTitleCell.setCellStyle(style);
365 
        affSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
366 
 
367 
        affSheet.createRow(affSerialNo ++);
368 
        Row affNameRow = affSheet.createRow(affSerialNo ++);
369 
        Cell affNameCell = affNameRow.createCell(0);
370 
        affNameCell.setCellValue("Affiliate : " + mAffName);
371 
        affNameCell.setCellStyle(style);
372 
        affSheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 6));
373 
 
374 
        Row affDateRangeRow = affSheet.createRow(affSerialNo ++);
375 
        Cell affDateRangeCell = affDateRangeRow.createCell(0);
376 
        affDateRangeCell.setCellValue("Date Range : " + df.format(startDate) + " - " + df.format(endDate));
377 
        affDateRangeCell.setCellStyle(style);
378 
        affSheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 6));
379 
 
380 
        affSheet.createRow(affSerialNo ++);affSheet.createRow(affSerialNo ++);
381 
 
382 
        Row affHeaderRow = affSheet.createRow(affSerialNo++);
383 
        affHeaderRow.createCell(0).setCellValue("Date");
384 
        affHeaderRow.createCell(1).setCellValue("ProductName");
4598 anupam.sin 385 
        affHeaderRow.createCell(2).setCellValue("Transaction Id");
3315 vikas 386 
        affHeaderRow.createCell(3).setCellValue("Amount");
387 
        for (int i=0; i<4 ;i++) {
388 
            affHeaderRow.getCell(i).setCellStyle(style);
389 
        }
390 
 
3343 vikas 391 
        Double totalQuantity = 0d;
392 
        Double totalAmount = 0d;
3315 vikas 393 
        for(Date date : dates) {
4598 anupam.sin 394 
            if (!dateTxnProductMap.containsKey(date)) {
3315 vikas 395 
                continue;
396 
            }
4598 anupam.sin 397 
            //HashSet<String> tmpProductSet = new HashSet<String>();
4657 anupam.sin 398 
            ArrayList<String> prodList = new ArrayList<String>();
4598 anupam.sin 399 
            Row commContentRow = null;
4657 anupam.sin 400 
            for (Entry<Long, ArrayList<String>> txnEntry : dateTxnProductMap.get(date).entrySet()) {
401 
                prodList = txnEntry.getValue();
4598 anupam.sin 402 
                Long txn = txnEntry.getKey();
4657 anupam.sin 403 
                for (String prodName : prodList) {
4674 anupam.sin 404 
                    affSerialNo++;
4657 anupam.sin 405 
                    commContentRow = affSheet.createRow(affSerialNo);
406 
                    commContentRow.createCell(0).setCellValue(date);
407 
                    commContentRow.getCell(0).setCellStyle(dateCellStyle);
408 
                    commContentRow.createCell(1).setCellValue(prodName);
409 
                    commContentRow.createCell(2).setCellValue(txn);
410 
                    Double amount = productAmountMap.get(date).get(prodName);
411 
                    amount = amount/(productQuantityMap.get(date).get(prodName));
412 
                    totalAmount += amount;
413 
                    commContentRow.createCell(3).setCellValue(amount);
414 
                    totalQuantity++;
415 
                }
3315 vikas 416 
            }
417 
        }
3343 vikas 418 
        affSerialNo+=2;
419 
        Row commContentRow = affSheet.createRow(affSerialNo);
420 
        commContentRow.createCell(0).setCellValue("Total");
421 
        commContentRow.createCell(1).setCellValue("");
422 
        commContentRow.createCell(2).setCellValue(totalQuantity);
423 
        commContentRow.createCell(3).setCellValue(totalAmount);
424 
 
425 
        for (int i = 0; i<4; i++) {
426 
            affSheet.autoSizeColumn(i);
427 
        }
3315 vikas 428 
    }
429 
 
430 
    private void createSummarySheet(String mAffName,
431 
            SortedSet<Date> dates,
432 
            Map<Date, Long> registerCountMap,
433 
            Map<Date, Double> saleQuantityMap,
434 
            Map<Date, Double> saleAmountMap,
435 
            Workbook wb,
436 
            CellStyle style,
437 
            CellStyle dateCellStyle)
438 
    {
439 
		// Summary SHEET
440 
	    Sheet affSheet = wb.createSheet("Summary Report");
1901 vikas 441 
	    short affSerialNo = 0;
3315 vikas 442 
 
443 
	    DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
1901 vikas 444 
 
445 
	    Row affTitleRow = affSheet.createRow(affSerialNo ++);
446 
	    Cell affTitleCell = affTitleRow.createCell(0);
3315 vikas 447 
	    affTitleCell.setCellValue("Daily Stats Report");
1901 vikas 448 
	    affTitleCell.setCellStyle(style);
449 
	    affSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
450 
 
451 
	    affSheet.createRow(affSerialNo ++);
3315 vikas 452 
	    Row affNameRow = affSheet.createRow(affSerialNo ++);
453 
        Cell affNameCell = affNameRow.createCell(0);
454 
        affNameCell.setCellValue("Affiliate : " + mAffName);
455 
        affNameCell.setCellStyle(style);
456 
        affSheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 6));
457 
 
458 
        Row affDateRangeRow = affSheet.createRow(affSerialNo ++);
459 
        Cell affDateRangeCell = affDateRangeRow.createCell(0);
460 
        affDateRangeCell.setCellValue("Date Range : " + df.format(startDate) + " - " + df.format(endDate));
461 
        affDateRangeCell.setCellStyle(style);
462 
        affSheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 6));
463 
 
464 
	    affSheet.createRow(affSerialNo ++);affSheet.createRow(affSerialNo ++);
1901 vikas 465 
 
466 
	    Row affHeaderRow = affSheet.createRow(affSerialNo++);
3315 vikas 467 
	    affHeaderRow.createCell(0).setCellValue("Date");
468 
	    affHeaderRow.createCell(1).setCellValue("New Registrations");
469 
	    affHeaderRow.createCell(2).setCellValue("Sales Count");
470 
	    affHeaderRow.createCell(3).setCellValue("Amount");
471 
	    for (int i=0; i<4 ;i++) {
1901 vikas 472 
	        affHeaderRow.getCell(i).setCellStyle(style);
473 
	    }
474 
 
3343 vikas 475 
	    Double totalregisterCount = 0d;
476 
        Double totalQuantity = 0d;
477 
	    Double totalAmount = 0d;
478 
        for(Date date : dates) {
3315 vikas 479 
		    affSerialNo++;
1901 vikas 480 
			Row commContentRow = affSheet.createRow(affSerialNo);
3315 vikas 481 
			Long registerCount = registerCountMap.get(date);
482 
			Double saleQuantity = saleQuantityMap.get(date);
483 
			Double saleAmount = saleAmountMap.get(date);
484 
			if (registerCount == null) {
485 
			    registerCount = 0l;
2340 vikas 486 
			}
3315 vikas 487 
			if (saleQuantity == null) {
488 
                saleQuantity = 0d;
489 
            }
490 
			if (saleAmount == null) {
491 
                saleAmount = 0d;
492 
            }
3343 vikas 493 
			totalregisterCount += registerCount;
494 
            totalQuantity += saleQuantity;
495 
			totalAmount += saleAmount;
496 
            commContentRow.createCell(0).setCellValue(date);
3315 vikas 497 
			commContentRow.getCell(0).setCellStyle(dateCellStyle);
498 
            commContentRow.createCell(1).setCellValue(registerCount);
499 
			commContentRow.createCell(2).setCellValue(saleQuantity);
500 
			commContentRow.createCell(3).setCellValue(saleAmount);
1901 vikas 501 
		}
3343 vikas 502 
        affSerialNo+=2;
503 
        Row commContentRow = affSheet.createRow(affSerialNo);
504 
        commContentRow.createCell(0).setCellValue("Total");
505 
        commContentRow.createCell(1).setCellValue(totalregisterCount);
506 
        commContentRow.createCell(2).setCellValue(totalQuantity);
507 
        commContentRow.createCell(3).setCellValue(totalAmount);
508 
 
3315 vikas 509 
        for (int i = 0; i<4; i++) {
1901 vikas 510 
            affSheet.autoSizeColumn(i);
511 
        }
512 
	}
513 
 
514 
	public String getErrorMsg() {
515 
		return errorMsg;
516 
	}
517 
 
518 
	public List<MasterAffiliate> getMasterAffiliates() {
519 
        return masterAffiliates;
520 
    }
2340 vikas 521 
}