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.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;

	}

}