Subversion Repositories SmartDukaan

package in.shop2020.catalog.util;

import in.shop2020.catalog.dashboard.shared.Item;
import in.shop2020.catalog.dashboard.shared.VendorPricings;
import in.shop2020.config.ConfigException;
import in.shop2020.model.v1.catalog.CatalogService;
import in.shop2020.model.v1.catalog.ItemType;
import in.shop2020.model.v1.catalog.status;
import in.shop2020.model.v1.inventory.InventoryService.Client;
import in.shop2020.model.v1.inventory.Vendor;
import in.shop2020.thrift.clients.CatalogClient;
import in.shop2020.thrift.clients.InventoryClient;
import in.shop2020.thrift.clients.config.ConfigClient;
import in.shop2020.utils.CategoryManager;
import in.shop2020.utils.ConfigClientKeys;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
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;

public class ReportGenerator {

        private static Logger logger = Logger.getLogger(ReportGenerator.class);
        public static int TYPE_INACTIVE = 0;
        public static int TYPE_BREAKEVEN = 1;
        public static int TYPE_BOTH = 2;
        Map<String, String> configMap = getConfigdataforPriceCompare();

        public ByteArrayOutputStream generateExcelStream(int type) {
                ByteArrayOutputStream baosXLS = new ByteArrayOutputStream();

                DecimalFormat numberFormat = new DecimalFormat("#.##");
                
                Workbook wb = new HSSFWorkbook();

                Font font = wb.createFont();
                font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                CellStyle styleBold = wb.createCellStyle();
                styleBold.setFont(font);

                CellStyle styleRedBG = wb.createCellStyle();
                styleRedBG.setFont(font);
                styleRedBG.setFillBackgroundColor(HSSFColor.RED.index);

                CellStyle styleWT = wb.createCellStyle();
                styleWT.setWrapText(true);
                short rowNo = 0;
                
        List<in.shop2020.model.v1.catalog.Item> thriftItemList = new ArrayList<in.shop2020.model.v1.catalog.Item>();
        List<in.shop2020.model.v1.catalog.Item> thriftItemListInactive = new ArrayList<in.shop2020.model.v1.catalog.Item>();
        try {
            CatalogClient catalogServiceClient = new CatalogClient(ConfigClientKeys.catalog_service_server_host_prod.toString(),
                    ConfigClientKeys.catalog_service_server_port.toString());
            CatalogService.Client catalogClient = catalogServiceClient.getClient();
            
            InventoryClient inventoryServiceClient = new InventoryClient();
            Client inventoryClient = inventoryServiceClient.getClient();
            List<Vendor> vendors = inventoryClient.getAllVendors();
            Map<Long, String> vendorIdNameMap = new Hashtable<Long, String>();
            for(Vendor vendor : vendors){
                vendorIdNameMap.put(vendor.getId(), vendor.getName());
            }
            thriftItemListInactive.addAll(catalogClient.getAllItemsByStatus(status.PAUSED));
            if(TYPE_INACTIVE != type){
                catalogServiceClient = new CatalogClient(ConfigClientKeys.catalog_service_server_host_prod.toString(),
                        ConfigClientKeys.catalog_service_server_port.toString());
                catalogClient = catalogServiceClient.getClient();
                    thriftItemList.addAll(catalogClient.getAllItemsByStatus(status.ACTIVE));
                
                    catalogServiceClient = new CatalogClient(ConfigClientKeys.catalog_service_server_host_prod.toString(),
                        ConfigClientKeys.catalog_service_server_port.toString());
                catalogClient = catalogServiceClient.getClient();
                    thriftItemList.addAll(catalogClient.getAllItemsByStatus(status.PAUSED_BY_RISK));
                    thriftItemList.addAll(thriftItemListInactive);
                    Sheet ngpSheet = wb.createSheet("Negative Gross Profit");
                    rowNo = 0;
                    
                    Row headerRow = ngpSheet.createRow(rowNo);
                    headerRow.createCell(0).setCellValue("Item Id");
                    headerRow.getCell(0).setCellStyle(styleBold);
                    headerRow.createCell(1).setCellValue("Brand");
                    headerRow.getCell(1).setCellStyle(styleBold);
                    headerRow.createCell(2).setCellValue("Model Name");
                    headerRow.getCell(2).setCellStyle(styleBold);
                    headerRow.createCell(3).setCellValue("Model Number");
                    headerRow.getCell(3).setCellStyle(styleBold);
                    headerRow.createCell(4).setCellValue("Color");
                    headerRow.getCell(4).setCellStyle(styleBold);
                    headerRow.createCell(5).setCellValue("Transfer Price");
                    headerRow.getCell(5).setCellStyle(styleBold);
                    headerRow.createCell(6).setCellValue("Vendor");
                    headerRow.getCell(6).setCellStyle(styleBold);
                    headerRow.createCell(7).setCellValue("Selling Price");
                    headerRow.getCell(7).setCellStyle(styleBold);
                    headerRow.createCell(8).setCellValue("Break Even Price");
                    headerRow.getCell(8).setCellStyle(styleBold);
                    headerRow.createCell(9).setCellValue("Current Status");
                    headerRow.getCell(9).setCellStyle(styleBold);
                    headerRow.createCell(10).setCellValue("Reason");
                    headerRow.getCell(10).setCellStyle(styleBold);
                    for(in.shop2020.model.v1.catalog.Item thriftItem : thriftItemList) {
                        List<in.shop2020.model.v1.inventory.VendorItemPricing> vip = inventoryClient.getAllItemPricing(thriftItem.getId());
                        Item item = getItemFromThriftItem(thriftItem, vip);
                        List<Object> breakeven = isBreakeven(item);
                        if(!(Boolean)breakeven.get(0)){
                                try{
                                Row itemRow = ngpSheet.createRow(++rowNo);
                                itemRow.createCell(0).setCellValue(item.getId());
                                itemRow.createCell(1).setCellValue(item.getBrand());
                                itemRow.createCell(2).setCellValue(item.getModelName());
                                itemRow.createCell(3).setCellValue(item.getModelNumber());
                                itemRow.createCell(4).setCellValue(item.getColor());
                                
                                try{
                                        itemRow.createCell(5).setCellValue(numberFormat.format((Double)breakeven.get(2)));
                                } catch (Exception e){
                                        itemRow.createCell(5).setCellValue("Empty");
                                }
                                
                                try{
                                        itemRow.createCell(6).setCellValue(vendorIdNameMap.get(breakeven.get(3)));
                                } catch (Exception e){
                                        itemRow.createCell(6).setCellValue("Empty");
                                }
                                
                                Double sellingPrice = item.getSellingPrice();
                                if(sellingPrice != null){
                                        itemRow.createCell(7).setCellValue(numberFormat.format(sellingPrice));
                                } else {
                                        itemRow.createCell(7).setCellValue("NULL");
                                }
                                
                                try{
                                        itemRow.createCell(8).setCellValue(numberFormat.format((Double)breakeven.get(1)));
                                } catch (Exception e){
                                        itemRow.createCell(8).setCellValue("Empty");
                                }
                                itemRow.createCell(9).setCellValue(thriftItem.getItemStatus().toString());
                                try { 
                                        itemRow.createCell(10).setCellValue((String)breakeven.get(1));
                                }catch (Exception e) {
                                        itemRow.createCell(10).setCellValue("");                                                        
                                                }
                                }catch (Exception e) {
                                                        logger.info("Error occurred for item : " + item.getId());
                                                        System.out.println("Item id failed for breakeven report: " + item.getId());
                                                }
                        }
                    }
            }
            
            
            if(TYPE_BREAKEVEN != type){
                catalogServiceClient = new CatalogClient(ConfigClientKeys.catalog_service_server_host_prod.toString(),
                         ConfigClientKeys.catalog_service_server_port.toString());
                catalogClient = catalogServiceClient.getClient();
                
                thriftItemListInactive.addAll(catalogClient.getAllItemsByStatus(status.PHASED_OUT));
                Sheet inactiveItemsSheet = wb.createSheet("Inactive Items with Positive Inventory");
                    rowNo = 0;
                    Row anotherHeaderRow = inactiveItemsSheet.createRow(0);
                    anotherHeaderRow.createCell(0).setCellValue("Item Id");
                    anotherHeaderRow.getCell(0).setCellStyle(styleBold);
                    anotherHeaderRow.createCell(1).setCellValue("Brand");
                    anotherHeaderRow.getCell(1).setCellStyle(styleBold);
                    anotherHeaderRow.createCell(2).setCellValue("Model Name");
                    anotherHeaderRow.getCell(2).setCellStyle(styleBold);
                    anotherHeaderRow.createCell(3).setCellValue("Model Number");
                    anotherHeaderRow.getCell(3).setCellStyle(styleBold);
                    anotherHeaderRow.createCell(4).setCellValue("Color");
                    anotherHeaderRow.getCell(4).setCellStyle(styleBold);
                    anotherHeaderRow.createCell(5).setCellValue("Status");
                    anotherHeaderRow.getCell(5).setCellStyle(styleBold);
                    anotherHeaderRow.createCell(6).setCellValue("Available Pieces");
                    anotherHeaderRow.getCell(6).setCellStyle(styleBold);
                                for (in.shop2020.model.v1.catalog.Item thriftItem : thriftItemListInactive) {
                                        try {
                                                in.shop2020.model.v1.inventory.ItemInventory itemInventory = inventoryClient.getItemInventoryByItemId(thriftItem.getId());
                                                long available = 0;
                                                for(long count : itemInventory.getAvailability().values()){
                                                        available += count;
                                                }
                                                for(long count : itemInventory.getReserved().values()){
                                                        available -= count;
                                                }
                                                if(available > 0){
                                                        Row itemRow = inactiveItemsSheet.createRow(++rowNo);
                                        itemRow.createCell(0).setCellValue(thriftItem.getId());
                                        itemRow.createCell(1).setCellValue(thriftItem.getBrand());
                                        itemRow.createCell(2).setCellValue(thriftItem.getModelName());
                                        itemRow.createCell(3).setCellValue(thriftItem.getModelNumber());
                                        itemRow.createCell(4).setCellValue(thriftItem.getColor());
                                        itemRow.createCell(5).setCellValue(thriftItem.getItemStatus().toString());
                                        itemRow.createCell(6).setCellValue(available);
                                                }
                                        } catch (Exception e) {
                                                logger.error("Error getting live inventory for item" + thriftItem.getId() + "\n"
                                                                + e);
                                        }
                                }
            }
            try {
                wb.write(baosXLS);
                baosXLS.close();
            } catch (IOException e) {
                e.printStackTrace();
                throw e;
            }
            
        } catch (Exception e) {
                e.printStackTrace();
                baosXLS = null;
            logger.error("Error getting items from Catalog\n" + e);
        }
        
        
                return baosXLS;
        }

        private List<Object> isBreakeven(Item newItem) {
                List<Object> list = new ArrayList<Object>();
                boolean message = true;
                Long vendor = 0L;

                if (newItem.getSellingPrice() == null || newItem.getSellingPrice().compareTo(0d) <= 0) {
                        message=false;
                        list.add(message);
                        list.add("Selling price is empty or 0");
                        return list;
                }

                double transferPrice;
                if (newItem.getPreferredVendor() == null
                                && !newItem.getVendorPricesMap().isEmpty()) {
                        transferPrice = -1;
                        for (VendorPricings vendorDetail : newItem.getVendorPricesMap()
                                        .values()) {
                                if (transferPrice > vendorDetail.getTransferPrice()
                                                || transferPrice == -1) {
                                        transferPrice = vendorDetail.getTransferPrice();
                                        vendor = vendorDetail.getVendorId();
                                }
                        }
                } else if (!newItem.getVendorPricesMap().isEmpty()
                                && newItem.getVendorPricesMap().containsKey(
                                                newItem.getPreferredVendor())) {
                        transferPrice = newItem.getVendorPricesMap()
                                        .get(newItem.getPreferredVendor()).getTransferPrice();
                        vendor = newItem.getVendorPricesMap().get(newItem.getPreferredVendor()).getVendorId();
                } else {
                        message = false;
                        list.add(message);
                        list.add("Preferred vendor is not in Vendor Item Mapping.");
                        return list;
                }
                
                double breakeven;
                
                //if weight is not mention add courier cost as Rs. 60
                if (newItem.getWeight() == null) {
                        breakeven = transferPrice + 60;
                } else {
                
                        double weightfactor = Math.ceil((newItem.getWeight() * 1000)
                                        / Double.parseDouble(configMap.get("courier_weight_factor")));
                        double couriercost = Double.parseDouble(configMap
                                        .get("courier_cost_factor")) * weightfactor;
                        double costfactor = (Double.parseDouble(configMap
                                        .get("transfer_price_percentage")) * transferPrice) / 100;
                        if (costfactor < Double.parseDouble(configMap
                                        .get("transfer_price_factor"))) {
                                breakeven = transferPrice
                                                + couriercost
                                                + Double.parseDouble(configMap
                                                                .get("breakeven_additon_factor"));
                        } else {
                                breakeven = (transferPrice + couriercost)
                                                / Double.parseDouble(configMap.get("breakeven_divisor"));
                        }
                }

                if (Double.compare(breakeven, newItem.getSellingPrice())>=0) {
                        message=false;
                        list.add(message);
                        list.add(breakeven);
                        list.add(transferPrice);
                        list.add(vendor);
                        return list;
                }
                list.add(message);
                return list;
        }

        private Map<String, String> getConfigdataforPriceCompare() {
                Map<String, String> ConfigMap = new HashMap<String, String>();
                try {
                        ConfigMap.put(
                                        "courier_cost_factor",
                                        ConfigClient.getClient().get(
                                                        ConfigClientKeys.courier_cost_factor.toString()));
                        ConfigMap.put("courier_weight_factor", ConfigClient.getClient()
                                        .get(ConfigClientKeys.courier_weight_factor.toString()));
                        ConfigMap.put(
                                        "breakeven_divisor",
                                        ConfigClient.getClient().get(
                                                        ConfigClientKeys.breakeven_divisor.toString()));
                        ConfigMap.put("breakeven_additon_factor", ConfigClient.getClient()
                                        .get(ConfigClientKeys.breakeven_additon_factor.toString()));
                        ConfigMap
                                        .put("transfer_price_percentage",
                                                        ConfigClient.getClient().get(
                                                                        ConfigClientKeys.transfer_price_percentage
                                                                                        .toString()));
                        ConfigMap.put("transfer_price_factor", ConfigClient.getClient()
                                        .get(ConfigClientKeys.transfer_price_factor.toString()));
                } catch (ConfigException ce) {
                        logger.error(
                                        "Unable to connect to the config server. Setting sensible defaults.",
                                        ce);
                        ConfigMap.put("courier_cost_factor", "60");
                        ConfigMap.put("courier_weight_factor", "500");
                        ConfigMap.put("breakeven_divisor", "0.98");
                        ConfigMap.put("breakeven_additon_factor", "50");
                        ConfigMap.put("transfer_price_percentage", "2");
                        ConfigMap.put("transfer_price_factor", "50");
                }
                return ConfigMap;
        }

        /**
         * Creates a new Item object and populates its attributes from thrift item
         * passed as parameter. Also creates a Map each for VendorItemPricing,
         * VendorItemMapping, SourceItemPricing and SimilarItems and adds these maps
         * to the new Item object.
         * 
         * @param thriftItem
         * @param tVendorPricings
         * @param tVendorMappings
         * @param tSourceMappings
         * @param tSimilarItems
         * @return item object with attributes copied from thrift item object.
         */
        private Item getItemFromThriftItem(
                        in.shop2020.model.v1.catalog.Item thriftItem,
                        List<in.shop2020.model.v1.inventory.VendorItemPricing> tVendorPricings) {

                Map<Long, VendorPricings> vendorPricingMap = new HashMap<Long, VendorPricings>();
                VendorPricings vPricings;
                if (tVendorPricings != null) {
                        for (in.shop2020.model.v1.inventory.VendorItemPricing vip : tVendorPricings) {
                                vPricings = new VendorPricings();
                                vPricings.setVendorId(vip.getVendorId());
                                vPricings.setMop(vip.getMop());
                                vPricings.setDealerPrice(vip.getDealerPrice());
                                vPricings.setTransferPrice(vip.getTransferPrice());
                                vendorPricingMap.put(vPricings.getVendorId(), vPricings);
                        }
                }

                in.shop2020.catalog.dashboard.shared.ItemStockPurchaseParams stockPurchaseParams = 
                new in.shop2020.catalog.dashboard.shared.ItemStockPurchaseParams();
                
                Item item = new Item(thriftItem.getId(), thriftItem.getProductGroup(),
                                thriftItem.getBrand(), thriftItem.getModelNumber(),
                                thriftItem.getModelName(), thriftItem.getColor(),
                                CategoryManager.getCategoryManager().getCategoryLabel(
                                                thriftItem.getCategory()), thriftItem.getCategory(),
                                thriftItem.getComments(), thriftItem.getCatalogItemId(),
                                thriftItem.getFeatureId(), thriftItem.getFeatureDescription(),
                                thriftItem.isSetMrp() ? thriftItem.getMrp() : null,
                                thriftItem.isSetSellingPrice() ? thriftItem.getSellingPrice()
                                                : null,
                                thriftItem.isSetWeight() ? thriftItem.getWeight() : null,
                                thriftItem.getAddedOn(), thriftItem.getStartDate(),
                                thriftItem.getComingSoonStartDate(), thriftItem.getExpectedArrivalDate(),
                                thriftItem.getRetireDate(), thriftItem.getUpdatedOn(),
                                thriftItem.getItemStatus().name(), thriftItem.getItemStatus()
                                                .getValue(), thriftItem.getStatus_description(),
                                thriftItem.getBestDealText(),
                                thriftItem.getBestDealsDetailsText(),
                                thriftItem.getBestDealsDetailsLink(),
                                thriftItem.isSetBestDealValue() ? thriftItem.getBestDealValue()
                                                : null,
                                thriftItem.isSetBestSellingRank() ? thriftItem
                                                .getBestSellingRank() : null,
                                thriftItem.isDefaultForEntity(), thriftItem.isRisky(),
                                thriftItem.isSetExpectedDelay() ? thriftItem.getExpectedDelay()
                                                : null,
                                thriftItem.isIsWarehousePreferenceSticky(),
                                thriftItem.isHasItemNo(),
                ItemType.SERIALIZED.equals(thriftItem.getType()),
                thriftItem.isSetShowSellingPrice() ? thriftItem.isShowSellingPrice() : false,
                thriftItem.isSetHoldOverride() ? thriftItem.isHoldOverride() : false,
                                thriftItem.isSetPreferredVendor() ? thriftItem
                                                .getPreferredVendor() : null,
                                thriftItem.isSetPreferredInsurer() ? thriftItem.getPreferredInsurer() : null, null,
                                vendorPricingMap, null, null, null, null, stockPurchaseParams.getNumOfDaysStock(), 
                                stockPurchaseParams.getMinStockLevel(), null, null,thriftItem.getAsin());
                return item;
        }

}