Subversion Repositories SmartDukaan

/**

 * 

 */

package in.shop2020.inventory.controllers;

import in.shop2020.model.v1.catalog.CatalogService;

import in.shop2020.model.v1.inventory.AmazonFbaInventorySnapshot;

import in.shop2020.model.v1.inventory.InventoryService;

import in.shop2020.thrift.clients.CatalogClient;

import in.shop2020.thrift.clients.InventoryClient;

import in.shop2020.thrift.clients.WarehouseClient;

import in.shop2020.warehouse.AmazonTransferredSkuDetail;

import in.shop2020.warehouse.InvAgeConsiderItems;

import in.shop2020.warehouse.InventoryAge;

import in.shop2020.warehouse.InventoryAvailability;

import in.shop2020.warehouse.Scan;

import in.shop2020.warehouse.ScanType;

import in.shop2020.warehouse.WarehouseService.Client;

import java.io.BufferedInputStream;

import java.io.BufferedWriter;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileReader;

import java.io.FileWriter;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.Collections;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

import javax.servlet.ServletOutputStream;

import org.apache.commons.lang.StringUtils;

import org.apache.commons.logging.Log;

import org.apache.commons.logging.LogFactory;

/**

 * @author mandeep

 *

 */

public class InventoryAgeController extends BaseController {

	private static Log logger = LogFactory.getLog(InventoryAgeController.class);

	public String index() {

		try {

			List<InventoryAge> inventoryAge = getSorplInventoryAge();

			List<InventoryAge> inventoryAgeAmazon = getAmazonInventoryAge();

			byte[] buffer = null;

			File file = createFile(inventoryAge, inventoryAgeAmazon);

			Thread.sleep(30000);

			buffer = new byte[(int) file.length()];

			InputStream input = null;

			try {

				int totalBytesRead = 0;

				input = new BufferedInputStream(new FileInputStream(file));

				while (totalBytesRead < buffer.length) {

					int bytesRemaining = buffer.length - totalBytesRead;

					// input.read() returns -1, 0, or more :

					int bytesRead = input.read(buffer, totalBytesRead,

							bytesRemaining);

					if (bytesRead > 0) {

						totalBytesRead = totalBytesRead + bytesRead;

					}

				}

				/*

				 * the above style is a bit tricky: it places bytes into the

				 * 'buffer' array; 'buffer' is an output parameter; the while

				 * loop usually has a single iteration only.

				 */

			} finally {

				input.close();

			}

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

			response.setHeader("Content-disposition", "inline; filename="

					+ file.getName());

			ServletOutputStream sos = response.getOutputStream();

			sos.write(buffer);

			sos.flush();      

		}

		catch (Exception e) {

		}

		return null;

	}

	public List<InventoryAge> getSorplInventoryAge(){

		List<InventoryAge> inventoryAge = new ArrayList<InventoryAge>();

		List<InventoryAvailability> serializedInventoryAvailability = new ArrayList<InventoryAvailability>();

		List<InventoryAvailability> nonSerializedInventoryAvailability = new ArrayList<InventoryAvailability>();

		Map<Long, InventoryAvailability> inventoryAvailabilityMap = new HashMap<Long, InventoryAvailability>();

		try {

			WarehouseClient whClient = new WarehouseClient();

			Client client = whClient.getClient();

			for(Long physicalWarehouseId : PHYSICAL_WAREHOUSES ){

				if(!client.isAlive()){

					client = whClient.getClient();

				}

				serializedInventoryAvailability = client.getCurrentSerializedInventoryByScans(physicalWarehouseId);

				nonSerializedInventoryAvailability  = client.getCurrentNonSerializedInventoryByScans(physicalWarehouseId);

				for(InventoryAvailability availability : serializedInventoryAvailability){

					if(inventoryAvailabilityMap.containsKey(availability.getItemId())){

						InventoryAvailability invAvailbility = inventoryAvailabilityMap.get(availability.getItemId());

						invAvailbility.setQuantity(invAvailbility.getQuantity()+availability.getQuantity());

						inventoryAvailabilityMap.put(invAvailbility.getItemId(), invAvailbility);

					}else{

						inventoryAvailabilityMap.put(availability.getItemId(), availability);

					}

				}

				for(InventoryAvailability availability : nonSerializedInventoryAvailability){

					if(inventoryAvailabilityMap.containsKey(availability.getItemId())){

						InventoryAvailability invAvailbility = inventoryAvailabilityMap.get(availability.getItemId());

						invAvailbility.setQuantity(invAvailbility.getQuantity()+availability.getQuantity());

						inventoryAvailabilityMap.put(invAvailbility.getItemId(), invAvailbility);

					}else{

						inventoryAvailabilityMap.put(availability.getItemId(), availability);

					}

				}

			}

			for(Long itemId : inventoryAvailabilityMap.keySet()){

				InventoryAvailability stockAvailability = inventoryAvailabilityMap.get(itemId);

				long stockQuantity = stockAvailability.getQuantity();

				logger.info("Item Id Key : "+ itemId + " Stock Quantity : "+ stockAvailability.getQuantity());

				if(!client.isAlive()){

					client = whClient.getClient();

				}

				List<InvAgeConsiderItems> invAgeConsiderItems = client.getInventoryAgeConsideredItems(itemId);

				List<InvAgeConsiderItems> toBeIncludedInvAgeConsiderItems = new ArrayList<InvAgeConsiderItems>();

				long freshCount = 0;

				long oneToTwoCount = 0;

				long twoToThreeCount = 0;

				long threeToFourCount = 0;

				long fourPlusCount = 0;

				long onePlusCount = 0;

				long threeMonthPlusCount = 0;

				long sixMonthPlusCount = 0;

				long zeroToThreeMonthCount = 0;

				long threeToSixMonthsCount = 0;

				long sixToTwelveMonthsCount = 0;

				long twelveMonthsPlusCount = 0;					

				long onePlusCost = 0;

				long zeroPlusCount = 0;

				long zeroPlusCost = 0;

				String brand =stockAvailability.getBrand();

				String modelName =stockAvailability.getModelName();

				String modelNumber =stockAvailability.getModelNumber();

				String color =stockAvailability.getColor();

				String category ="";

				if(invAgeConsiderItems!=null && invAgeConsiderItems.size()>0){

					category = invAgeConsiderItems.get(0).getCategory();

				}

				if(invAgeConsiderItems==null || invAgeConsiderItems.size()==0){

					continue;

				}

				double invAge = 0.0;

				for(InvAgeConsiderItems invItem : invAgeConsiderItems){

					if(stockQuantity == invItem.getCurrentQuantity()){

						toBeIncludedInvAgeConsiderItems.add(invItem);

						break;

					}

					if(stockQuantity > invItem.getCurrentQuantity()){

						toBeIncludedInvAgeConsiderItems.add(invItem);

						stockQuantity = stockQuantity - invItem.getCurrentQuantity();

						continue;

					}

					if(stockQuantity < invItem.getCurrentQuantity()){

						invItem.setCurrentQuantity(stockQuantity);

						toBeIncludedInvAgeConsiderItems.add(invItem);

						break;

					}

				}

				for(InvAgeConsiderItems invItem : toBeIncludedInvAgeConsiderItems){

					invAge = invItem.getAge();

					if(invAge < 1){

						freshCount = freshCount + invItem.getCurrentQuantity();

					}

					if(invAge >= 1 && invAge < 2){

						oneToTwoCount = oneToTwoCount + invItem.getCurrentQuantity();

					}

					if(invAge >= 2 && invAge < 3){

						twoToThreeCount = twoToThreeCount + invItem.getCurrentQuantity();

					}

					if(invAge >= 3 && invAge < 4){

						threeToFourCount = threeToFourCount + invItem.getCurrentQuantity();

					}

					if(invAge >= 4){

						fourPlusCount = fourPlusCount + invItem.getCurrentQuantity();

					}

					if(invAge >= 1){

						onePlusCount = onePlusCount + invItem.getCurrentQuantity();

						onePlusCost = onePlusCost + (invItem.getCost() * invItem.getCurrentQuantity());

					}

					if(invAge >= 13){

						threeMonthPlusCount = threeMonthPlusCount + invItem.getCurrentQuantity();

					}

					if(invAge >= 26){

						sixMonthPlusCount = sixMonthPlusCount + invItem.getCurrentQuantity();

					}

					if(invAge >=0 && invAge <=13){

						zeroToThreeMonthCount = zeroToThreeMonthCount + invItem.getCurrentQuantity();

					}

					if(invAge >13 && invAge <=26){

						threeToSixMonthsCount = threeToSixMonthsCount + invItem.getCurrentQuantity();

					}

					if(invAge >26 && invAge <=52){

						sixToTwelveMonthsCount = sixToTwelveMonthsCount + invItem.getCurrentQuantity();

					}

					if(invAge > 52){

						twelveMonthsPlusCount = twelveMonthsPlusCount + invItem.getCurrentQuantity();

					}

					zeroPlusCount = zeroPlusCount + invItem.getCurrentQuantity();

					zeroPlusCost = zeroPlusCost + (invItem.getCost() * invItem.getCurrentQuantity());

				}

				InventoryAge inventAge = new InventoryAge();

				inventAge.setItemId(itemId);

				inventAge.setBrand(brand);

				inventAge.setModelName(modelName);

				inventAge.setModelNumber(modelNumber);

				inventAge.setColor(color);

				inventAge.setFreshCount(freshCount);

				inventAge.setOneToTwoCount(oneToTwoCount);

				inventAge.setTwoToThreeCount(twoToThreeCount);

				inventAge.setThreeToFourCount(threeToFourCount);

				inventAge.setFourPlusCount(fourPlusCount);

				inventAge.setThreeMonthPlusCount(threeMonthPlusCount);

				inventAge.setSixMonthPlusCount(sixMonthPlusCount);

				inventAge.setZeroToThreeMonthCount(zeroToThreeMonthCount);

				inventAge.setThreeToSixMonthCount(threeToSixMonthsCount);

				inventAge.setSixToTwelveMonthCount(sixToTwelveMonthsCount);

				inventAge.setTwelveMonthsPlusCount(twelveMonthsPlusCount);

				inventAge.setZeroPlusCount(zeroPlusCount);

				inventAge.setOnePlusCount(onePlusCount);

				inventAge.setZeroPlusCost(zeroPlusCost);

				inventAge.setOnePlusCost(onePlusCost);

				inventAge.setCategory(category);

				inventoryAge.add(inventAge);

			}

		}

		catch (Exception e) {

			e.printStackTrace();

		}

		return inventoryAge;

	}

	/**

	 * @param inventoryAge

	 * @return

	 */

	private File createFile(List<InventoryAge> inventoryAge, List<InventoryAge> inventoryAgeAmazon) {

		Map<Long,InventoryAge> inventoryAgeMap = new HashMap<Long,InventoryAge>();

		for(InventoryAge invAge : inventoryAge){

			inventoryAgeMap.put(invAge.getItemId(), invAge);

		}

		for(InventoryAge invAge : inventoryAgeAmazon){

			if(inventoryAgeMap.containsKey(invAge.getItemId())){

				InventoryAge invAgeObj = inventoryAgeMap.get(invAge.getItemId());

				invAgeObj.setFreshCount(invAgeObj.getFreshCount()+ invAge.getFreshCount());

				invAgeObj.setOneToTwoCount(invAgeObj.getOneToTwoCount() + invAge.getOneToTwoCount());

				invAgeObj.setTwoToThreeCount(invAgeObj.getTwoToThreeCount() + invAge.getTwoToThreeCount());

				invAgeObj.setThreeToFourCount(invAgeObj.getThreeToFourCount() + invAge.getThreeToFourCount());

				invAgeObj.setFourPlusCount(invAgeObj.getFourPlusCount() + invAge.getFourPlusCount());

				invAgeObj.setThreeMonthPlusCount(invAgeObj.getThreeMonthPlusCount() + invAge.getThreeMonthPlusCount());

				invAgeObj.setSixMonthPlusCount(invAgeObj.getSixMonthPlusCount() + invAge.getSixMonthPlusCount());

				invAgeObj.setZeroToThreeMonthCount(invAgeObj.getZeroToThreeMonthCount() + invAge.getZeroToThreeMonthCount());

				invAgeObj.setThreeToSixMonthCount(invAgeObj.getThreeToSixMonthCount() + invAge.getThreeToSixMonthCount());

				invAgeObj.setSixToTwelveMonthCount(invAgeObj.getSixToTwelveMonthCount() + invAge.getSixToTwelveMonthCount());

				invAgeObj.setTwelveMonthsPlusCount(invAgeObj.getTwelveMonthsPlusCount() + invAge.getTwelveMonthsPlusCount());

				invAgeObj.setZeroPlusCount(invAgeObj.getZeroPlusCount() + invAge.getZeroPlusCount());

				invAgeObj.setOnePlusCount(invAgeObj.getOnePlusCount() + invAge.getOnePlusCount());

				invAgeObj.setZeroPlusCost(invAgeObj.getZeroPlusCost() + invAge.getZeroPlusCost());

				invAgeObj.setOnePlusCost(invAgeObj.getOnePlusCost() + invAge.getOnePlusCost());

				inventoryAgeMap.put(invAge.getItemId(), invAgeObj);

			}

			else{

				inventoryAgeMap.put(invAge.getItemId(), invAge);

			}

		}

		List<InventoryAge> finalInventoryAge = new ArrayList<InventoryAge>();

		for(Long itemId : inventoryAgeMap.keySet()){

			finalInventoryAge.add(inventoryAgeMap.get(itemId));

		}

		try {

			File file = new File("/tmp/InventoryAge.xls");

			BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(file));

			bufferedWriter.write(StringUtils.join(new String[] {

					"Item Id",

					"Category",

					"Brand",

					"Model Name",

					"Model Number",

					"Color",

					"Fresh",

					"1-2 week",

					"2-3 week",

					"3-4 week",

					"4+ week",

					"3+ month",

					"6+ month",

					"0-3 month",

					"3-6 month",

					"6-12 month",

					"12+ month",

					"1+ week",

					"1+ week cost",

					"All",

			"All cost" }, '\t'));

			for (InventoryAge item : finalInventoryAge) {

				bufferedWriter.newLine();

				bufferedWriter.write(StringUtils.join(

						new String[] {

								String.valueOf(item.getItemId()),

								item.getCategory(),

								item.getBrand(),

								item.getModelName(),

								item.getModelNumber(),

								item.getColor(),

								String.valueOf(item.getFreshCount()),

								String.valueOf(item.getOneToTwoCount()),

								String.valueOf(item.getTwoToThreeCount()),

								String.valueOf(item.getThreeToFourCount()),

								String.valueOf(item.getFourPlusCount()),

								String.valueOf(item.getThreeMonthPlusCount()),

								String.valueOf(item.getSixMonthPlusCount()),

								String.valueOf(item.getZeroToThreeMonthCount()),

								String.valueOf(item.getThreeToSixMonthCount()),

								String.valueOf(item.getSixToTwelveMonthCount()),

								String.valueOf(item.getTwelveMonthsPlusCount()),

								String.valueOf(item.getOnePlusCount()),

								String.valueOf(item.getOnePlusCost()),

								String.valueOf(item.getZeroPlusCount()),

								String.valueOf(item.getZeroPlusCost())}, '\t'));

			}

			bufferedWriter.close();

			return file;

		} catch (Exception e) {

			return null;

		}

	}

	public List<InventoryAge> getAmazonInventoryAge(){

		Long currentTimestamp = System.currentTimeMillis();

		List<InventoryAge> amazonInventoryAge = new ArrayList<InventoryAge>();

		try{

			InventoryService.Client inventoryClient = new InventoryClient().getClient();

			List<AmazonFbaInventorySnapshot> amazonFbaInventorySnapshot = inventoryClient.getAllAmazonFbaItemInventory();

			List<Long> itemIds = new ArrayList<Long>();

			for(AmazonFbaInventorySnapshot invSnapShot : amazonFbaInventorySnapshot){

				//logger.info("AmazonFbaInventorySnapshot entry " + invSnapShot.getItem_id());

				itemIds.add(invSnapShot.getItem_id());

			}

			Client client = new WarehouseClient().getClient();

			List<AmazonTransferredSkuDetail> amazonTransferredSkuDetails = client.getAmazonTransferredSkuDetails(itemIds);

			Map<Long, AmazonFbaInventorySnapshot> amazonFbaSnapshotMap = new HashMap<Long, AmazonFbaInventorySnapshot> ();

			Map<Long, List<AmazonTransferredSkuDetail>> skuDetailsMap = new HashMap<Long, List<AmazonTransferredSkuDetail>>();

			for(AmazonFbaInventorySnapshot invSnapShot : amazonFbaInventorySnapshot){

				Long itemId = invSnapShot.getItem_id();

				if(!amazonFbaSnapshotMap.containsKey(itemId)){

					amazonFbaSnapshotMap.put(itemId, invSnapShot);

					//logger.info("Adding Availabity " + itemId + " Availability " +invSnapShot.getAvailability());

				} else {

					AmazonFbaInventorySnapshot invSS = amazonFbaSnapshotMap.get(itemId);

					invSnapShot.setAvailability(invSnapShot.getAvailability() + invSS.getAvailability());

					amazonFbaSnapshotMap.put(itemId, invSnapShot);

					//logger.info("Updating Availabity " + itemId + " Availability " +invSnapShot.getAvailability());

				}

				List<AmazonTransferredSkuDetail> mappedList = new ArrayList<AmazonTransferredSkuDetail>();

				for(AmazonTransferredSkuDetail detail: amazonTransferredSkuDetails){

					if(itemId.longValue() == detail.getItemId()){

						logger.info("Amazon Transferred Sku Details Detail found for ITEM ID  " + itemId.longValue()+" " + itemId);

						mappedList.add(detail);

					}

					else{

					}

				}

				skuDetailsMap.put(itemId, mappedList);

			}

			//CatalogService.Client catalogClient = new CatalogClient().getClient();

			for(AmazonFbaInventorySnapshot invSnapShot : amazonFbaSnapshotMap.values()){

				try{

					Long item_id = invSnapShot.getItem_id();

					List<AmazonTransferredSkuDetail> amazonTranSkuDetails = skuDetailsMap.get(item_id);

					long amazonFbaInvAvailability = invSnapShot.getAvailability();

					List<AmazonTransferredSkuDetail> toBeIncludeList = new ArrayList<AmazonTransferredSkuDetail>();

					long freshCount = 0;

					long oneToTwoCount = 0;

					long twoToThreeCount = 0;

					long threeToFourCount = 0;

					long fourPlusCount = 0;

					long onePlusCount = 0;

					long threeMonthPlusCount = 0;

					long sixMonthPlusCount = 0;

					long zeroToThreeMonthCount = 0;

					long threeToSixMonthsCount = 0;

					long sixToTwelveMonthsCount = 0;

					long twelveMonthsPlusCount = 0;					

					long onePlusCost = 0;

					long zeroPlusCount = 0;

					long zeroPlusCost = 0;

					String brand ="";

					String modelName ="";

					String modelNumber ="";

					String color ="";

					String category ="";

					if(amazonTranSkuDetails!=null && amazonTranSkuDetails.size()>0){

						brand = amazonTranSkuDetails.get(0).getBrand();

						modelName = amazonTranSkuDetails.get(0).getModelName();

						modelNumber = amazonTranSkuDetails.get(0).getModelNumber();

						color = amazonTranSkuDetails.get(0).getColor();

						category = amazonTranSkuDetails.get(0).getCategory();

					}else{	

						logger.info("Amazon Sku Transfer details  skipped " + item_id);

						continue;

					}

					double invAge = 0;

					for(AmazonTransferredSkuDetail amazonSkuDetail : amazonTranSkuDetails){

						if(amazonFbaInvAvailability == amazonSkuDetail.getQuantity()){

							toBeIncludeList.add(amazonSkuDetail);

							break;

						}

						if(amazonFbaInvAvailability > amazonSkuDetail.getQuantity()){

							toBeIncludeList.add(amazonSkuDetail);

							amazonFbaInvAvailability = amazonFbaInvAvailability- amazonSkuDetail.getQuantity();

							continue;

						}

						if(amazonFbaInvAvailability < amazonSkuDetail.getQuantity()){

							amazonSkuDetail.setQuantity(amazonFbaInvAvailability);

							toBeIncludeList.add(amazonSkuDetail);

							break;

						}

					}

					for(AmazonTransferredSkuDetail amazonSkuInfo : toBeIncludeList){

						if(!amazonSkuInfo.isSetPurchaseDate() || amazonSkuInfo.getPurchaseDate() == 0){

							if(!client.isAlive()){

								client = new WarehouseClient().getClient();

							}

							List<Scan> purchaseScans = client.getScansforPurchase(amazonSkuInfo.getPurchaseId(), ScanType.PURCHASE);

							amazonSkuInfo.setPurchaseDate(purchaseScans.get(0).getScannedAt());

						}

						invAge = (double)(currentTimestamp - amazonSkuInfo.getPurchaseDate())/ (24 * 60 * 60 * 1000 * 7);

						if(invAge < 1){

							freshCount = freshCount + amazonSkuInfo.getQuantity();

						}

						if(invAge >= 1 && invAge < 2){

							oneToTwoCount = oneToTwoCount + amazonSkuInfo.getQuantity();

						}

						if(invAge >= 2 && invAge < 3){

							twoToThreeCount = twoToThreeCount + amazonSkuInfo.getQuantity();

						}

						if(invAge >= 3 && invAge < 4){

							threeToFourCount = threeToFourCount + amazonSkuInfo.getQuantity();

						}

						if(invAge >= 4){

							fourPlusCount = fourPlusCount + amazonSkuInfo.getQuantity();

						}

						if(invAge >= 1){

							onePlusCount = onePlusCount + amazonSkuInfo.getQuantity();

							onePlusCost = (long)(onePlusCost + ( amazonSkuInfo.getQuantity() * amazonSkuInfo.getUnitPrice() ));

						}

						if(invAge >= 13){

							threeMonthPlusCount = threeMonthPlusCount + amazonSkuInfo.getQuantity();

						}

						if(invAge >= 26){

							sixMonthPlusCount = sixMonthPlusCount + amazonSkuInfo.getQuantity();

						}

						if(invAge >=0 && invAge <=13){

							zeroToThreeMonthCount = zeroToThreeMonthCount + amazonSkuInfo.getQuantity();

						}

						if(invAge >13 && invAge <=26){

							threeToSixMonthsCount = threeToSixMonthsCount + amazonSkuInfo.getQuantity();

						}

						if(invAge >26 && invAge <=52){

							sixToTwelveMonthsCount = sixToTwelveMonthsCount + amazonSkuInfo.getQuantity();

						}

						if(invAge > 52){

							twelveMonthsPlusCount = twelveMonthsPlusCount + amazonSkuInfo.getQuantity();

						}

						zeroPlusCount = zeroPlusCount + amazonSkuInfo.getQuantity(); 

						zeroPlusCost = (long)(zeroPlusCost + ( amazonSkuInfo.getQuantity() * amazonSkuInfo.getUnitPrice() ));

					}

					InventoryAge inventAge = new InventoryAge();

					inventAge.setItemId(item_id);

					inventAge.setBrand(brand);

					inventAge.setModelName(modelName);

					inventAge.setModelNumber(modelNumber);

					inventAge.setColor(color);

					inventAge.setFreshCount(freshCount);

					inventAge.setOneToTwoCount(oneToTwoCount);

					inventAge.setTwoToThreeCount(twoToThreeCount);

					inventAge.setThreeToFourCount(threeToFourCount);

					inventAge.setFourPlusCount(fourPlusCount);

					inventAge.setThreeMonthPlusCount(threeMonthPlusCount);

					inventAge.setSixMonthPlusCount(sixMonthPlusCount);

					inventAge.setZeroToThreeMonthCount(zeroToThreeMonthCount);

					inventAge.setThreeToSixMonthCount(threeToSixMonthsCount);

					inventAge.setSixToTwelveMonthCount(sixToTwelveMonthsCount);

					inventAge.setTwelveMonthsPlusCount(twelveMonthsPlusCount);

					inventAge.setZeroPlusCount(zeroPlusCount);

					inventAge.setOnePlusCount(onePlusCount);

					inventAge.setZeroPlusCost(zeroPlusCost);

					inventAge.setOnePlusCost(onePlusCost);

					inventAge.setCategory(category);

					amazonInventoryAge.add(inventAge);

				}

				catch(Exception e){

					e.printStackTrace();				

				}

			}

		}

		catch(Exception e){

			e.printStackTrace();				

		}

		return amazonInventoryAge;

	}

	public File createFile(List<InventoryAge> inventoryAge, String name){

		try {

			File file = new File(name);

			BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(file));

			bufferedWriter.write(StringUtils.join(new String[] {

					"Item Id",

					"Category",

					"Brand",

					"Model Name",

					"Model Number",

					"Color",

					"Fresh",

					"1-2 week",

					"2-3 week",

					"3-4 week",

					"4+ week",

					"3+ month",

					"6+ month",

					"0-3 month",

					"3-6 month",

					"6-12 month",

					"12+ month",

					"1+ week",

					"1+ week cost",

					"All",

			"All cost" }, '\t'));

			for (InventoryAge item : inventoryAge) {

				bufferedWriter.newLine();

				bufferedWriter.write(StringUtils.join(

						new String[] {

								String.valueOf(item.getItemId()),

								item.getCategory(),

								item.getBrand(),

								item.getModelName(),

								item.getModelNumber(),

								item.getColor(),

								String.valueOf(item.getFreshCount()),

								String.valueOf(item.getOneToTwoCount()),

								String.valueOf(item.getTwoToThreeCount()),

								String.valueOf(item.getThreeToFourCount()),

								String.valueOf(item.getFourPlusCount()),

								String.valueOf(item.getThreeMonthPlusCount()),

								String.valueOf(item.getSixMonthPlusCount()),

								String.valueOf(item.getZeroToThreeMonthCount()),

								String.valueOf(item.getThreeToSixMonthCount()),

								String.valueOf(item.getSixToTwelveMonthCount()),

								String.valueOf(item.getTwelveMonthsPlusCount()),

								String.valueOf(item.getOnePlusCount()),

								String.valueOf(item.getOnePlusCost()),

								String.valueOf(item.getZeroPlusCount()),

								String.valueOf(item.getZeroPlusCost())}, '\t'));

			}

			bufferedWriter.close();

			return file;

		} catch (Exception e) {

			return null;

		}

	}

	public void downloadAmazonInventoryAge(){

		try {

			List<InventoryAge> inventoryAgeAmazon = getAmazonInventoryAge();

			byte[] buffer = null;

			File file = createFile(inventoryAgeAmazon,"/tmp/AmazonInventoryAge.xls");

			Thread.sleep(10000);

			buffer = new byte[(int) file.length()];

			InputStream input = null;

			try {

				int totalBytesRead = 0;

				input = new BufferedInputStream(new FileInputStream(file));

				while (totalBytesRead < buffer.length) {

					int bytesRemaining = buffer.length - totalBytesRead;

					// input.read() returns -1, 0, or more :

					int bytesRead = input.read(buffer, totalBytesRead,

							bytesRemaining);

					if (bytesRead > 0) {

						totalBytesRead = totalBytesRead + bytesRead;

					}

				}

				/*

				 * the above style is a bit tricky: it places bytes into the

				 * 'buffer' array; 'buffer' is an output parameter; the while

				 * loop usually has a single iteration only.

				 */

			} finally {

				input.close();

			}

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

			response.setHeader("Content-disposition", "inline; filename="

					+ file.getName());

			ServletOutputStream sos = response.getOutputStream();

			sos.write(buffer);

			sos.flush();      

		}

		catch (Exception e) {

		}

	}

	public void downloadSaholicInventoryAge(){

		try {

			List<InventoryAge> inventoryAge = getSorplInventoryAge();

			byte[] buffer = null;

			File file = createFile(inventoryAge,"/tmp/SaholicInventoryAge.xls");

			Thread.sleep(10000);

			buffer = new byte[(int) file.length()];

			InputStream input = null;

			try {

				int totalBytesRead = 0;

				input = new BufferedInputStream(new FileInputStream(file));

				while (totalBytesRead < buffer.length) {

					int bytesRemaining = buffer.length - totalBytesRead;

					// input.read() returns -1, 0, or more :

					int bytesRead = input.read(buffer, totalBytesRead,

							bytesRemaining);

					if (bytesRead > 0) {

						totalBytesRead = totalBytesRead + bytesRead;

					}

				}

				/*

				 * the above style is a bit tricky: it places bytes into the

				 * 'buffer' array; 'buffer' is an output parameter; the while

				 * loop usually has a single iteration only.

				 */

			} finally {

				input.close();

			}

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

			response.setHeader("Content-disposition", "inline; filename="

					+ file.getName());

			ServletOutputStream sos = response.getOutputStream();

			sos.write(buffer);

			sos.flush();      

		}

		catch (Exception e) {

		}

	}

	public static void main(String[] args) throws Exception{

		List<InventoryAge> inventoryAge = new InventoryAgeController().getSorplInventoryAge();

	}

}