Subversion Repositories SmartDukaan

Rev

Rev 6791 | Blame | Compare with Previous | Last modification | View Log | RSS feed 

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 Date getCorrectedDate(Date uf){
        Calendar cal = Calendar.getInstance();  
        cal.setTime(uf);        
        cal.set(Calendar.HOUR_OF_DAY, 0);  
        cal.set(Calendar.MINUTE, 0);  
        cal.set(Calendar.SECOND, 0);  
        cal.set(Calendar.MILLISECOND, 0);
        return cal.getTime();
    }
      
      
  
    
    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(getCorrectedDate(new Date(order.getCreated_timestamp())));
            commContentRow.getCell(7).setCellStyle(dateCellStyle);
            
            commContentRow.createCell(8).setCellValue(getCorrectedDate(new Date(order.getPromised_shipping_time())));
            commContentRow.getCell(8).setCellStyle(dateCellStyle);
            
            commContentRow.createCell(9).setCellValue(getCorrectedDate(new Date(order.getShipping_timestamp())));
            commContentRow.getCell(9).setCellStyle(dateCellStyle);
            
            if (order.getFirst_attempt_timestamp() != 0) {
                commContentRow.createCell(10).setCellValue(getCorrectedDate(new Date(order.getFirst_attempt_timestamp())));
                commContentRow.getCell(10).setCellStyle(dateCellStyle);
            } else {
                commContentRow.createCell(10).setCellValue("N/A");
            }
            
            commContentRow.createCell(11).setCellValue(getCorrectedDate(new Date(order.getPromised_delivery_time())));
            commContentRow.getCell(11).setCellStyle(dateCellStyle);
            
            commContentRow.createCell(12).setCellValue(getCorrectedDate(new Date(order.getExpected_delivery_time())));
            commContentRow.getCell(12).setCellStyle(dateCellStyle);
            
            commContentRow.createCell(13).setCellValue(getCorrectedDate(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(getCorrectedDate(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(getCorrectedDate(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(getCorrectedDate(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(getCorrectedDate(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(getCorrectedDate(new Date(order.getCreated_timestamp())));
            commContentRow.getCell(7).setCellStyle(dateCellStyle);
            
            if (order.getPromised_shipping_time() != 0) {
                commContentRow.createCell(8).setCellValue(getCorrectedDate(new Date(order.getPromised_shipping_time())));
                commContentRow.getCell(8).setCellStyle(dateCellStyle);
            } else {
                commContentRow.createCell(8).setCellValue("N/A");
            }
            
            commContentRow.createCell(9).setCellValue(getCorrectedDate(new Date(order.getShipping_timestamp())));
            commContentRow.getCell(9).setCellStyle(dateCellStyle);
            
            if (order.getFirst_attempt_timestamp() != 0) {
                commContentRow.createCell(10).setCellValue(getCorrectedDate(new Date(order.getFirst_attempt_timestamp())));
                commContentRow.getCell(10).setCellStyle(dateCellStyle);
            } else {
                commContentRow.createCell(10).setCellValue("N/A");
            }
            
            commContentRow.createCell(11).setCellValue(getCorrectedDate(new Date(order.getPromised_delivery_time())));
            commContentRow.getCell(11).setCellStyle(dateCellStyle);
            
            if (order.getDelivery_timestamp() != 0) { 
                commContentRow.createCell(12).setCellValue(getCorrectedDate(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(getCorrectedDate(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(getCorrectedDate(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;
    }
}