Subversion Repositories SmartDukaan

package in.shop2020.support.controllers;

import java.io.ByteArrayOutputStream;

import java.io.IOException;

import java.text.DateFormat;

import java.text.ParseException;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Calendar;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import java.util.TimeZone;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.CellStyle;

import org.apache.poi.ss.usermodel.CreationHelper;

import org.apache.poi.ss.usermodel.Font;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.util.CellRangeAddress;

import org.apache.struts2.interceptor.ServletRequestAware;

import org.apache.struts2.interceptor.ServletResponseAware;

import org.apache.thrift.TException;

import org.apache.thrift.transport.TTransportException;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import in.shop2020.logistics.LogisticsServiceException;

import in.shop2020.logistics.Provider;

import in.shop2020.model.v1.order.LineItem;

import in.shop2020.model.v1.order.Order;

import in.shop2020.model.v1.order.OrderStatus;

import in.shop2020.model.v1.order.TransactionServiceException;

import in.shop2020.support.utils.ReportsUtils;

import in.shop2020.thrift.clients.LogisticsClient;

import in.shop2020.thrift.clients.TransactionClient;

public class CourierPerformanceController implements ServletRequestAware, ServletResponseAware {

    private List<OrderStatus> rtoStatuses;

    private List<Order> orders;

    private static Logger log = LoggerFactory.getLogger(CourierPerformanceController.class);

    private HttpServletResponse response;

    private HttpServletRequest request;

    private HttpSession session;

    private String errorMsg;

    private Date startDate;

    private Date endDate;

    private Map<Long, String> logisticProviderMap;

    public CourierPerformanceController() {

        rtoStatuses = new ArrayList<OrderStatus>();

        rtoStatuses.add(OrderStatus.RTO_IN_TRANSIT);

        rtoStatuses.add(OrderStatus.RTO_RECEIVED_PRESTINE);

        rtoStatuses.add(OrderStatus.RTO_RESHIPPED);

        rtoStatuses.add(OrderStatus.RTO_REFUNDED);

        rtoStatuses.add(OrderStatus.RTO_RECEIVED_DAMAGED);

        rtoStatuses.add(OrderStatus.RTO_LOST_IN_TRANSIT);

        rtoStatuses.add(OrderStatus.RTO_DAMAGED_RESHIPPED);

        rtoStatuses.add(OrderStatus.RTO_DAMAGED_REFUNDED);

        rtoStatuses.add(OrderStatus.RTO_LOST_IN_TRANSIT_RESHIPPED);

        rtoStatuses.add(OrderStatus.RTO_LOST_IN_TRANSIT_REFUNDED);

        rtoStatuses.add(OrderStatus.RTO_INVENTORY_REVERSED);

    }

    public String index() {

        log.info(request.getServletPath());

        if (!ReportsUtils.canAccessReport(

                (Long) session.getAttribute(ReportsUtils.ROLE),

                request.getServletPath())) 

        {

            return "authfail";

        }

        return "index";

    }

    public String create() {

        try {

            logisticProviderMap = new HashMap<Long, String>();

            LogisticsClient lc = new LogisticsClient();

            List<Provider> providers = lc.getClient().getAllProviders();    

            for (Provider provider : providers) {

                logisticProviderMap.put(provider.getId(), provider.getName());

            }

            String startDateStr = request.getParameter("startDate");

            String endDateStr = request.getParameter("endDate");

            DateFormat df = new SimpleDateFormat("MM/dd/yyyy");

            df.setTimeZone(TimeZone.getTimeZone("IST"));

            try {

                startDate = df.parse(startDateStr);

                endDate = df.parse(endDateStr);

                //Calendar cal = Calendar.getInstance();

                //cal.setTime(endDate);

                //endDate.setTime(cal.getTimeInMillis());

            } catch (ParseException pe) {

                errorMsg = "Please enter start and end dates in format MM/dd/yyyy";

                return "index";

            }

            response.setContentType("application/vnd.ms-excel");

            response.setHeader("Content-disposition", "inline; filename=" + "courier-performance-report" + ".xls");

            ServletOutputStream sos;

            try {

                ByteArrayOutputStream baos = getSpreadSheetData();

                sos = response.getOutputStream();

                baos.writeTo(sos);

                sos.flush();

            } catch (IOException e) {

                log.error("Unable to stream the courier performance report", e);

                errorMsg = "Failed to write to response.";

            }

        } catch (TTransportException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } catch (TException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } catch (LogisticsServiceException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        return "index";

    }

    private ByteArrayOutputStream getSpreadSheetData() {

        ByteArrayOutputStream baosXLS = new ByteArrayOutputStream();

        Workbook wb = new HSSFWorkbook();

        Font font = wb.createFont();

        font.setBoldweight(Font.BOLDWEIGHT_BOLD);

        CellStyle style = wb.createCellStyle();

        style.setFont(font);

        CreationHelper createHelper = wb.getCreationHelper();

        CellStyle dateCellStyle = wb.createCellStyle();

        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("DD/MM/YYYY"));

        createRtoSheet(wb, style, dateCellStyle);

        createDeliveredSheet(wb, style, dateCellStyle);

        // Write the workbook to the output stream

        try {

            wb.write(baosXLS);

            baosXLS.close();

        } catch (IOException e) {

            log.error("Unable to get the byte array for the affiliate report", e);

        }       

        return baosXLS;

    }

    private void createRtoSheet(Workbook wb, 

                                       CellStyle style,

                                       CellStyle dateCellStyle) {

        // Summary SHEET

        Sheet sheet1 = wb.createSheet("RTO");

        short affSerialNo = 0;

        DateFormat df = new SimpleDateFormat("MM/dd/yyyy");

        Row affTitleRow = sheet1.createRow(affSerialNo ++);

        Cell affTitleCell = affTitleRow.createCell(0);

        affTitleCell.setCellValue("Courier Performance Report : RTO Orders");

        affTitleCell.setCellStyle(style);

        sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));

        sheet1.createRow(affSerialNo ++);

        Row affDateRangeRow = sheet1.createRow(affSerialNo ++);

        Cell affDateRangeCell = affDateRangeRow.createCell(0);

        affDateRangeCell.setCellValue("Date Range : " + df.format(startDate) + " - " + df.format(endDate));

        affDateRangeCell.setCellStyle(style);

        sheet1.addMergedRegion(new CellRangeAddress(3, 3, 0, 6));

        sheet1.createRow(affSerialNo ++);sheet1.createRow(affSerialNo ++);

        Row affHeaderRow = sheet1.createRow(affSerialNo++);

        affHeaderRow.createCell(0).setCellValue("Order Id");

        affHeaderRow.createCell(1).setCellValue("Payment Type");

        affHeaderRow.createCell(2).setCellValue("Product Name");

        affHeaderRow.createCell(3).setCellValue("AWB");

        affHeaderRow.createCell(4).setCellValue("Pincode");

        affHeaderRow.createCell(5).setCellValue("City");

        affHeaderRow.createCell(6).setCellValue("Logistics Provider");

        affHeaderRow.createCell(7).setCellValue("Order Created Date");

        affHeaderRow.createCell(8).setCellValue("Promised Shipping Date");

        affHeaderRow.createCell(9).setCellValue("Shipping Date");

        affHeaderRow.createCell(10).setCellValue("First Delivery Attempt");

        affHeaderRow.createCell(11).setCellValue("Promised Delivery Date");

        affHeaderRow.createCell(12).setCellValue("Expected Delivery Date");

        affHeaderRow.createCell(13).setCellValue("Expected Courier Delivery Date");

        affHeaderRow.createCell(14).setCellValue("RTO Declare date");

        affHeaderRow.createCell(15).setCellValue("Return Receive Date");

        affHeaderRow.createCell(16).setCellValue("RTO Refund Date");

        affHeaderRow.createCell(17).setCellValue("RTO Reship Date");

        affHeaderRow.createCell(18).setCellValue("Shipping to Return Time");

        affHeaderRow.createCell(19).setCellValue("First Attempt Delay");

        for (int i=0; i<20 ;i++) {

            affHeaderRow.getCell(i).setCellStyle(style);

        }

        try {

            TransactionClient tc = new TransactionClient();

            orders = tc.getClient().getAllOrders(rtoStatuses, startDate.getTime(), endDate.getTime(), 0);

        } catch (TransactionServiceException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } catch (TException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        for(Order order : orders) {

            long diffDays = -99;

            affSerialNo++;

            Row commContentRow = sheet1.createRow(affSerialNo);

            commContentRow.createCell(0).setCellValue(order.getId());

            commContentRow.createCell(1).setCellValue(order.isLogisticsCod() ? "COD" : "PREPAID");

            List<LineItem> items = order.getLineitems();

            String product = items.get(0).getBrand() + " " + items.get(0).getModel_name() + " " + items.get(0).getModel_number();

            commContentRow.createCell(2).setCellValue(product);

            commContentRow.createCell(3).setCellValue(order.getAirwaybill_no());

            commContentRow.createCell(4).setCellValue(order.getCustomer_pincode());

            commContentRow.createCell(5).setCellValue(order.getCustomer_city());

            commContentRow.createCell(6).setCellValue(logisticProviderMap.get(order.getLogistics_provider_id()));

            commContentRow.createCell(7).setCellValue(new Date(order.getCreated_timestamp()));

            commContentRow.getCell(7).setCellStyle(dateCellStyle);

            commContentRow.createCell(8).setCellValue(new Date(order.getPromised_shipping_time()));

            commContentRow.getCell(8).setCellStyle(dateCellStyle);

            commContentRow.createCell(9).setCellValue(new Date(order.getShipping_timestamp()));

            commContentRow.getCell(9).setCellStyle(dateCellStyle);

            if (order.getFirst_attempt_timestamp() != 0) {

                commContentRow.createCell(10).setCellValue(new Date(order.getFirst_attempt_timestamp()));

                commContentRow.getCell(10).setCellStyle(dateCellStyle);

            } else {

                commContentRow.createCell(10).setCellValue("N/A");

            }

            commContentRow.createCell(11).setCellValue(new Date(order.getPromised_delivery_time()));

            commContentRow.getCell(11).setCellStyle(dateCellStyle);

            commContentRow.createCell(12).setCellValue(new Date(order.getExpected_delivery_time()));

            commContentRow.getCell(12).setCellStyle(dateCellStyle);

            commContentRow.createCell(13).setCellValue(new Date(order.getCourier_delivery_time()));

            commContentRow.getCell(13).setCellStyle(dateCellStyle);

            if (order.getDelivery_timestamp() != 0) {

                //When our courier partners declare a shipment as RTO we mark that time as delivered timestamp in our DB.

                commContentRow.createCell(14).setCellValue(new Date(order.getDelivery_timestamp()));//This is actually RTO Declare date.

                commContentRow.getCell(14).setCellStyle(dateCellStyle);

            } else {

                commContentRow.createCell(14).setCellValue("N/A");

            }

            if (order.getReceived_return_timestamp() != 0) {

                commContentRow.createCell(15).setCellValue(new Date(order.getReceived_return_timestamp()));

                commContentRow.getCell(15).setCellStyle(dateCellStyle);

            } else {

                commContentRow.createCell(15).setCellValue("N/A");

            }

            if (order.getRefund_timestamp() != 0) {

                commContentRow.createCell(16).setCellValue(new Date(order.getRefund_timestamp()));

                commContentRow.getCell(16).setCellStyle(dateCellStyle);

            } else {

                commContentRow.createCell(16).setCellValue("N/A");

            }

            if (order.getReship_timestamp() != 0) {

                commContentRow.createCell(17).setCellValue(new Date(order.getReship_timestamp()));

                commContentRow.getCell(17).setCellStyle(dateCellStyle);

            } else {

                commContentRow.createCell(17).setCellValue("N/A");

            }

            if (order.getReceived_return_timestamp() != 0) {

                diffDays = GetDelayinDays(order.getReceived_return_timestamp(), order.getShipping_timestamp());

                commContentRow.createCell(18).setCellValue(diffDays);

            } else {

                commContentRow.createCell(18).setCellValue("N/A");

            }

            if (order.getFirst_attempt_timestamp() != 0) {

                diffDays = GetDelayinDays(order.getFirst_attempt_timestamp(), order.getCourier_delivery_time());

                commContentRow.createCell(19).setCellValue(diffDays);

            } else {

                commContentRow.createCell(19).setCellValue("N/A");

            }

        }

        for (int i = 0; i<20; i++) {

            sheet1.autoSizeColumn(i);

        }

    }

    private void createDeliveredSheet(Workbook wb, 

            CellStyle style,

            CellStyle dateCellStyle) {

        // Summary SHEET

        Sheet sheet2 = wb.createSheet("Delivered");

        short affSerialNo = 0;

        DateFormat df = new SimpleDateFormat("MM/dd/yyyy");

        Row affTitleRow = sheet2.createRow(affSerialNo ++);

        Cell affTitleCell = affTitleRow.createCell(0);

        affTitleCell.setCellValue("Courier Performance Report : Delivered Orders");

        affTitleCell.setCellStyle(style);

        sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));

        sheet2.createRow(affSerialNo ++);

        Row affDateRangeRow = sheet2.createRow(affSerialNo ++);

        Cell affDateRangeCell = affDateRangeRow.createCell(0);

        affDateRangeCell.setCellValue("Date Range : " + df.format(startDate) + " - " + df.format(endDate));

        affDateRangeCell.setCellStyle(style);

        sheet2.addMergedRegion(new CellRangeAddress(3, 3, 0, 6));

        sheet2.createRow(affSerialNo ++);sheet2.createRow(affSerialNo ++);

        Row affHeaderRow = sheet2.createRow(affSerialNo++);

        affHeaderRow.createCell(0).setCellValue("Order Id");

        affHeaderRow.createCell(1).setCellValue("Payment Type");

        affHeaderRow.createCell(2).setCellValue("Product Name");

        affHeaderRow.createCell(3).setCellValue("AWB");

        affHeaderRow.createCell(4).setCellValue("Pincode");

        affHeaderRow.createCell(5).setCellValue("City");

        affHeaderRow.createCell(6).setCellValue("Logistics Provider");

        affHeaderRow.createCell(7).setCellValue("Order Created Date");

        affHeaderRow.createCell(8).setCellValue("Promised Shipping Date");

        affHeaderRow.createCell(9).setCellValue("Shipping Date");

        affHeaderRow.createCell(10).setCellValue("First Delivery Attempt");

        affHeaderRow.createCell(11).setCellValue("Promised Delivery Date");

        affHeaderRow.createCell(12).setCellValue("Expected Delivery Date");

        affHeaderRow.createCell(13).setCellValue("Expected Courier Delivery Date");

        affHeaderRow.createCell(14).setCellValue("Delivery Date");

        affHeaderRow.createCell(15).setCellValue("Delay");

        affHeaderRow.createCell(16).setCellValue("First Attempt Delay");

        for (int i=0; i<16 ;i++) {

            affHeaderRow.getCell(i).setCellStyle(style);

        }

        try {

            TransactionClient tc = new TransactionClient();

            List<OrderStatus> deliveredStatuses = new ArrayList<OrderStatus>();

            deliveredStatuses.add(OrderStatus.DELIVERY_SUCCESS);

            orders = tc.getClient().getAllOrders(deliveredStatuses, startDate.getTime(), endDate.getTime(), 0);

        } catch (TransactionServiceException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        } catch (TException e) {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

        for(Order order : orders) {

            affSerialNo++;

            Row commContentRow = sheet2.createRow(affSerialNo);

            commContentRow.createCell(0).setCellValue(order.getId());

            commContentRow.createCell(1).setCellValue(order.isLogisticsCod() ? "COD" : "PREPAID");

            List<LineItem> items = order.getLineitems();

            String product = items.get(0).getBrand() + " " + items.get(0).getModel_name() + " " + items.get(0).getModel_number();

            commContentRow.createCell(2).setCellValue(product);

            commContentRow.createCell(3).setCellValue(order.getAirwaybill_no());

            commContentRow.createCell(4).setCellValue(order.getCustomer_pincode());

            commContentRow.createCell(5).setCellValue(order.getCustomer_city());

            commContentRow.createCell(6).setCellValue(logisticProviderMap.get(order.getLogistics_provider_id()));

            commContentRow.createCell(7).setCellValue(new Date(order.getCreated_timestamp()));

            commContentRow.getCell(7).setCellStyle(dateCellStyle);

            if (order.getPromised_shipping_time() != 0) {

                commContentRow.createCell(8).setCellValue(new Date(order.getPromised_shipping_time()));

                commContentRow.getCell(8).setCellStyle(dateCellStyle);

            } else {

                commContentRow.createCell(8).setCellValue("N/A");

            }

            commContentRow.createCell(9).setCellValue(new Date(order.getShipping_timestamp()));

            commContentRow.getCell(9).setCellStyle(dateCellStyle);

            if (order.getFirst_attempt_timestamp() != 0) {

                commContentRow.createCell(10).setCellValue(new Date(order.getFirst_attempt_timestamp()));

                commContentRow.getCell(10).setCellStyle(dateCellStyle);

            } else {

                commContentRow.createCell(10).setCellValue("N/A");

            }

            commContentRow.createCell(11).setCellValue(new Date(order.getPromised_delivery_time()));

            commContentRow.getCell(11).setCellStyle(dateCellStyle);

            if (order.getDelivery_timestamp() != 0) { 

                commContentRow.createCell(12).setCellValue(new Date(order.getExpected_delivery_time()));

                commContentRow.getCell(12).setCellStyle(dateCellStyle);

            } else {

                commContentRow.createCell(12).setCellValue("N/A");

            }

            if (order.getDelivery_timestamp() != 0) { 

                commContentRow.createCell(13).setCellValue(new Date(order.getCourier_delivery_time()));

                commContentRow.getCell(13).setCellStyle(dateCellStyle);

            } else {

                commContentRow.createCell(13).setCellValue("N/A");

            }

            if (order.getDelivery_timestamp() != 0) {    

                commContentRow.createCell(14).setCellValue(new Date(order.getDelivery_timestamp()));

                commContentRow.getCell(14).setCellStyle(dateCellStyle);

            } else {

                commContentRow.createCell(14).setCellValue("N/A");

            }

            //We are subtracting delay due to shipping from delay in delivery as it was not courier's

            //fault that the shipment was delayed

            long delayInShipping = 0;

            long diffDays = GetDelayinDays(order.getDelivery_timestamp(), order.getCourier_delivery_time());

            if (order.getPromised_shipping_time() != 0) {

                delayInShipping = GetDelayinDays(order.getShipping_timestamp(), order.getPromised_shipping_time());

                commContentRow.createCell(15).setCellValue(diffDays - delayInShipping);

            } else {

                commContentRow.createCell(15).setCellValue(diffDays);

            }

            diffDays = GetDelayinDays(order.getFirst_attempt_timestamp(), order.getCourier_delivery_time());

            if (order.getFirst_attempt_timestamp() != 0) { 

                if (order.getPromised_shipping_time() != 0) {

                    delayInShipping = GetDelayinDays(order.getShipping_timestamp(), order.getPromised_shipping_time());

                    commContentRow.createCell(16).setCellValue(diffDays - delayInShipping);

                }

            } else { 

                commContentRow.createCell(16).setCellValue("N/A");

            }

        }

        for (int i = 0; i<17; i++) {

            sheet2.autoSizeColumn(i);

        }

    }

    long GetDelayinDays(long actualDate, long expectedDate) {

        Calendar actualTime = Calendar.getInstance();

        Calendar expectedTime = Calendar.getInstance();

        long millisInDays = 24 * 60 * 60 * 1000;

        actualTime.setTimeInMillis(actualDate);

        expectedTime.setTimeInMillis(expectedDate);

        long actualTimeInMillis = actualTime.getTimeInMillis();

        long expectedTimeInMillis = expectedTime.getTimeInMillis();

        long diff = actualTimeInMillis - expectedTimeInMillis;

        long diffDays = diff / millisInDays;

        return diffDays;

    }

    public List<OrderStatus> getRtoStatuses() {

        return rtoStatuses;

    }

    public void setRtoStatuses(List<OrderStatus> rtoStatuses) {

        this.rtoStatuses = rtoStatuses;

    }

    public void setOrders(List<Order> orders) {

        this.orders = orders;

    }

    public List<Order> getOrders() {

        return orders;

    }

    @Override

    public void setServletResponse(HttpServletResponse res) {

        this.response = res;

    }

    @Override

    public void setServletRequest(HttpServletRequest req) {

        this.request = req;

        this.session = req.getSession();

    }

    public String getErrorMsg() {

        return errorMsg;

    }

    public void setErrorMsg(String errorMsg) {

        this.errorMsg = errorMsg;

    }

    public Date getStartDate() {

        return startDate;

    }

    public void setStartDate(Date startDate) {

        this.startDate = startDate;

    }

    public Date getEndDate() {

        return endDate;

    }

    public void setEndDate(Date endDate) {

        this.endDate = endDate;

    }

}