Subversion Repositories SmartDukaan

Rev

Rev 8293 | Rev 8378 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 8293 Rev 8363
Line 1... Line 1...
1 
package com.amazonaws.mws.samples;
 1 
package com.amazonaws.mws.samples;
2 
 
 2 
 
3 
import in.shop2020.model.v1.catalog.Amazonlisted;
 3 
import in.shop2020.model.v1.catalog.Amazonlisted;
4 
import in.shop2020.model.v1.catalog.CatalogServiceException;
 4 
import in.shop2020.model.v1.catalog.CatalogServiceException;
- 
 
 5 
import in.shop2020.model.v1.catalog.Category;
5 
import in.shop2020.model.v1.catalog.Item;
 6 
import in.shop2020.model.v1.catalog.Item;
6 
import in.shop2020.model.v1.inventory.AmazonFbaInventorySnapshot;
 7 
import in.shop2020.model.v1.inventory.AmazonFbaInventorySnapshot;
- 
 
 8 
import in.shop2020.model.v1.inventory.AmazonInventorySnapshot;
7 
import in.shop2020.model.v1.inventory.InventoryServiceException;
 9 
import in.shop2020.model.v1.inventory.InventoryServiceException;
- 
 
 10 
import in.shop2020.model.v1.inventory.ItemInventory;
8 
import in.shop2020.model.v1.inventory.Vendor;
 11 
import in.shop2020.model.v1.inventory.Vendor;
9 
import in.shop2020.model.v1.inventory.VendorItemPricing;
 12 
import in.shop2020.model.v1.inventory.VendorItemPricing;
10 
import in.shop2020.model.v1.order.AmazonFbaSalesSnapshot;
 13 
import in.shop2020.model.v1.order.AmazonFbaSalesSnapshot;
11 
import in.shop2020.model.v1.order.TransactionService.Client;
 14 
import in.shop2020.model.v1.order.TransactionService.Client;
12 
import in.shop2020.thrift.clients.CatalogClient;
 15 
import in.shop2020.thrift.clients.CatalogClient;
Line 25... Line 28...
25 
import java.util.List;
 28 
import java.util.List;
26 
import java.util.Map;
 29 
import java.util.Map;
27 
import java.util.Map.Entry;
 30 
import java.util.Map.Entry;
28 
import java.util.TimeZone;
 31 
import java.util.TimeZone;
29 
 
 32 
 
- 
 
 33 
import org.apache.poi.hssf.usermodel.HSSFCell;
- 
 
 34 
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
- 
 
 35 
import org.apache.poi.hssf.usermodel.HSSFFont;
30 
import org.apache.poi.hssf.usermodel.HSSFRow;
 36 
import org.apache.poi.hssf.usermodel.HSSFRow;
31 
import org.apache.poi.hssf.usermodel.HSSFSheet;
 37 
import org.apache.poi.hssf.usermodel.HSSFSheet;
32 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 38 
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- 
 
 39 
import org.apache.poi.hssf.util.HSSFColor;
33 
import org.apache.thrift.TException;
 40 
import org.apache.thrift.TException;
34 
 
 41 
 
35 
import au.com.bytecode.opencsv.CSVReader;
 42 
import au.com.bytecode.opencsv.CSVReader;
36 
 
 43 
 
37 
 
 44 
 
Line 48... Line 55...
48 
		}
55 
		}
49 
 
 56 
 
50 
		in.shop2020.model.v1.order.TransactionService.Client transactionClient   = transactionServiceClient.getClient();
 57 
		in.shop2020.model.v1.order.TransactionService.Client transactionClient   = transactionServiceClient.getClient();
51 
		List<AmazonFbaSalesSnapshot> salessnapshots = null;
 58 
		List<AmazonFbaSalesSnapshot> salessnapshots = null;
52 
		try {
 59 
		try {
53 
			salessnapshots = transactionClient.getAmazonFbaSalesSnapshotForDays(5);
 60 
			salessnapshots = transactionClient.getAmazonFbaSalesSnapshotForDays(4);
54 
		} catch (TException e) {
 61 
		} catch (TException e) {
55 
			// TODO Auto-generated catch block
 62 
			// TODO Auto-generated catch block
56 
			e.printStackTrace();
 63 
			e.printStackTrace();
57 
		}
 64 
		}
58 
		Map<Long,List<Integer>> itemId5daysSale = new HashMap<Long,List<Integer>>();
 65 
		Map<Long,List<FbaSalesSnapshot>> itemId5daysSale = new HashMap<Long,List<FbaSalesSnapshot>>();
59 
		AmazonFbaSalesSnapshot snapshot;
 66 
		AmazonFbaSalesSnapshot snapshot;
60 
		for(AmazonFbaSalesSnapshot salessnapshot:salessnapshots){
 67 
		for(AmazonFbaSalesSnapshot salessnapshot:salessnapshots){
61 
			try {
 68 
			try {
- 
 
 69 
				FbaSalesSnapshot fbaSalesSnapshot = new FbaSalesSnapshot();
62 
				if(itemId5daysSale.containsKey(salessnapshot.getItem_id())){
 70 
				if(itemId5daysSale.containsKey(salessnapshot.getItem_id())){
63 
					if(salessnapshot.getAmazonFbaInventory()!=0)
 71 
					if(!salessnapshot.isIsOutOfStock()){
- 
 
 72 
						fbaSalesSnapshot.setPromotionOrderCount(salessnapshot.getPromotionOrderCount());
64 
						itemId5daysSale.get(salessnapshot.getItem_id()).add(salessnapshot.getOrderCount());
 73 
						fbaSalesSnapshot.setTotalOrderCount(salessnapshot.getTotalOrderCount());
- 
 
 74 
						fbaSalesSnapshot.setTotalPromotionSale((float) salessnapshot.getPromotionSale());
- 
 
 75 
						fbaSalesSnapshot.setTotalSale((float) salessnapshot.getTotalSale());
- 
 
 76 
					}
65 
					else
 77 
					else{
- 
 
 78 
						fbaSalesSnapshot.setPromotionOrderCount(-1);
- 
 
 79 
						fbaSalesSnapshot.setTotalOrderCount(-1);
- 
 
 80 
						fbaSalesSnapshot.setTotalPromotionSale((float) 0);
- 
 
 81 
						fbaSalesSnapshot.setTotalSale((float) 0);
- 
 
 82 
					}
66 
						itemId5daysSale.get(salessnapshot.getItem_id()).add(-1);
 83 
					itemId5daysSale.get(salessnapshot.getItem_id()).add(fbaSalesSnapshot);
67 
				}
 84 
				}
68 
				else{
 85 
				else{
69 
					List<Integer> last5daysSale = new ArrayList<Integer>();
 86 
					List<FbaSalesSnapshot> last5daysSale = new ArrayList<FbaSalesSnapshot>();
70 
					if(salessnapshot.getAmazonFbaInventory()!=0){
 87 
					if(!salessnapshot.isIsOutOfStock()){
- 
 
 88 
						fbaSalesSnapshot.setPromotionOrderCount(salessnapshot.getPromotionOrderCount());
71 
						last5daysSale.add(salessnapshot.getOrderCount());
 89 
						fbaSalesSnapshot.setTotalOrderCount(salessnapshot.getTotalOrderCount());
- 
 
 90 
						fbaSalesSnapshot.setTotalPromotionSale((float) salessnapshot.getPromotionSale());
- 
 
 91 
						fbaSalesSnapshot.setTotalSale((float) salessnapshot.getTotalSale());
72 
					}
 92 
					}
73 
					else{
93 
					else{
- 
 
 94 
						fbaSalesSnapshot.setPromotionOrderCount(-1);
74 
						last5daysSale.add(-1);
 95 
						fbaSalesSnapshot.setTotalOrderCount(-1);
- 
 
 96 
						fbaSalesSnapshot.setTotalPromotionSale((float) 0);
- 
 
 97 
						fbaSalesSnapshot.setTotalSale((float) 0);
75 
					}
 98 
					}
- 
 
 99 
					last5daysSale.add(fbaSalesSnapshot);
76 
					itemId5daysSale.put(salessnapshot.getItem_id(),last5daysSale);
 100 
					itemId5daysSale.put(salessnapshot.getItem_id(),last5daysSale);
77 
				}
 101 
				}
78 
			} catch (Exception e) {
 102 
			} catch (Exception e) {
79 
				// TODO Auto-generated catch block
 103 
				// TODO Auto-generated catch block
80 
				e.printStackTrace();
 104 
				e.printStackTrace();
Line 96... Line 120...
96 
		in.shop2020.model.v1.inventory.InventoryService.Client inventoryClient = inventoryServiceClient.getClient();
 120 
		in.shop2020.model.v1.inventory.InventoryService.Client inventoryClient = inventoryServiceClient.getClient();
97 
		HSSFWorkbook hwb=new HSSFWorkbook();
 121 
		HSSFWorkbook hwb=new HSSFWorkbook();
98 
		HSSFSheet sheet =  hwb.createSheet("FBA-STOCK-SHEET");
 122 
		HSSFSheet sheet =  hwb.createSheet("FBA-STOCK-SHEET");
99 
		HSSFRow rowhead=   sheet.createRow((short)0);
 123 
		HSSFRow rowhead=   sheet.createRow((short)0);
100 
		rowhead.createCell((short) 0).setCellValue("ITEM-ID");
 124 
		rowhead.createCell((short) 0).setCellValue("ITEM-ID");
101 
		rowhead.createCell((short) 1).setCellValue("LAST 5 DAYS SALE");
 125 
		rowhead.createCell((short) 1).setCellValue("BRAND");
102 
		rowhead.createCell((short) 2).setCellValue("PRODUCT-NAME");
 126 
		rowhead.createCell((short) 2).setCellValue("CATEGORY");
103 
		rowhead.createCell((short) 3).setCellValue("TRANSFER-PRICE");
 127 
		rowhead.createCell((short) 3).setCellValue("PRODUCT-GROUP");
104 
		rowhead.createCell((short) 4).setCellValue("NLC");
 128 
		rowhead.createCell((short) 4).setCellValue("PRODUCT-NAME");
105 
		rowhead.createCell((short) 5).setCellValue("IS-STICKY");
 129 
		rowhead.createCell((short) 5).setCellValue("IS-STICKY");
106 
		rowhead.createCell((short) 6).setCellValue("PREFERRED-VENDOR");
 130 
		rowhead.createCell((short) 6).setCellValue("PREFERRED-VENDOR");
- 
 
 131 
		rowhead.createCell((short) 7).setCellValue("TRANSFER-PRICE");
107 
		rowhead.createCell((short) 7).setCellValue("QUANTITY");
 132 
		rowhead.createCell((short) 8).setCellValue("NLC");
- 
 
 133 
		rowhead.createCell((short) 9).setCellValue("LAST 5 DAYS TOTAL SALE");
- 
 
 134 
		rowhead.createCell((short) 10).setCellValue("TOTAL ASP");
- 
 
 135 
		rowhead.createCell((short) 11).setCellValue("LAST 5 DAYS PROMOTION SALE");
- 
 
 136 
		rowhead.createCell((short) 12).setCellValue("PROMOTION ASP");
108 
		rowhead.createCell((short) 8).setCellValue("MIN-AFN-PRICE");
 137 
		rowhead.createCell((short) 13).setCellValue("MIN-AFN-PRICE");
109 
		rowhead.createCell((short) 9).setCellValue("MIN-MFN-PRICE");
 138 
		rowhead.createCell((short) 14).setCellValue("MIN-MFN-PRICE");
110 
		rowhead.createCell((short) 10).setCellValue("OUR-PRICE");
 139 
		rowhead.createCell((short) 15).setCellValue("OUR-PRICE");
111 
		rowhead.createCell((short) 11).setCellValue("TOTAL-AMOUNT");
 140 
		rowhead.createCell((short) 16).setCellValue("STOCK-IN-MUMBAI");
- 
 
 141 
		rowhead.createCell((short) 17).setCellValue("AVERAGE-SALE");
112 
		rowhead.createCell((short) 12).setCellValue("DAYS-OF-STOCK");
 142 
		rowhead.createCell((short) 18).setCellValue("DAYS-OF-STOCK");
- 
 
 143 
		rowhead.createCell((short) 19).setCellValue("REQUIRED STOCK");
- 
 
 144 
		rowhead.createCell((short) 20).setCellValue("CURRENT-STOCK (FBA)");
- 
 
 145 
		rowhead.createCell((short) 21).setCellValue("P.O");
- 
 
 146 
		rowhead.createCell((short) 22).setCellValue("P.O AMOUNT");
- 
 
 147 
		rowhead.createCell((short) 23).setCellValue("VARIANCE-FROM-CHEAPEST");
- 
 
 148 
		rowhead.createCell((short) 24).setCellValue("DAYS-OF-STOCK-RULE");
113 
		int iterator=1;
 149 
		int iterator=1;
114 
		for(Map.Entry<Long,List<Integer>> entry : itemId5daysSale.entrySet()){
 150 
		Map<Long,ItemInventory> allItemsAvailability = null;
115 
			int count =1;
 - 
 
116 
			int avg_sale=0;
 151 
		List<Long> WarehouseIdsForMumbaiLocation = null;
117 
			int sale_days=0;
 152 
		try {
118 
			StringBuilder days_sale = new StringBuilder();
 153 
			WarehouseIdsForMumbaiLocation = inventoryClient.getOursGoodWarehouseIdsForLocation(1);
119 
			for(Integer sale:entry.getValue()){
 154 
			allItemsAvailability =	inventoryClient.getInventorySnapshot(0);
120 
				if(sale!=-1){
 155 
		} catch (TException e1) {
121 
					days_sale.append(sale.toString());
 156 
			// TODO Auto-generated catch block
122 
					avg_sale=avg_sale+sale;
 157 
			e1.printStackTrace();
123 
					sale_days++;
 - 
 
124 
				}
 - 
 
125 
				else{
 - 
 
126 
					days_sale.append("X");
 - 
 
127 
				}
 - 
 
128 
				if(count != entry.getValue().size()){
 - 
 
129 
					days_sale.append("-");
 - 
 
130 
				}
 - 
 
131 
				count++;
 - 
 
132 
			}
 158 
		}
133 
			if(sale_days!=0){
 - 
 
134 
				avg_sale=avg_sale/sale_days;
 159 
		for(Entry<Long, List<FbaSalesSnapshot>> entry : itemId5daysSale.entrySet()){
135 
			}
 - 
 
136 
			Item item = null;
 160 
			Item item = null;
137 
			VendorItemPricing vip=null;
 161 
			VendorItemPricing vip=null;
138 
			Vendor vendor = null;
 162 
			Vendor vendor = null;
- 
 
 163 
			Category category = null;
139 
			try {
 164 
			try {
140 
				//System.out.println("Item ID is " + entry.getKey());
 165 
				System.out.println("Item ID is " + entry.getKey());
141 
				item = catalogClient.getItem(entry.getKey());
 166 
				item = catalogClient.getItem(entry.getKey());
- 
 
 167 
				if(item.getId()==0){
- 
 
 168 
					continue;
- 
 
 169 
				}
- 
 
 170 
				category = catalogClient.getCategory(item.getCategory());
- 
 
 171 
				//System.out.println("Preferred Vendor is " + item.getPreferredVendor());
- 
 
 172 
				if(item.getPreferredVendor()!=0){
142 
				vip = inventoryClient.getItemPricing(item.getId(),item.getPreferredVendor());
 173 
					vip = inventoryClient.getItemPricing(item.getId(),item.getPreferredVendor());
143 
				vendor = inventoryClient.getVendor(item.getPreferredVendor());
 174 
					vendor = inventoryClient.getVendor(item.getPreferredVendor());
- 
 
 175 
				}
144 
			} catch (CatalogServiceException e) {
 176 
			} catch (CatalogServiceException e) {
145 
				// TODO Auto-generated catch block
 177 
				// TODO Auto-generated catch block
146 
				e.printStackTrace();
 178 
				e.printStackTrace();
147 
			} catch (TException e) {
 179 
			} catch (TException e) {
148 
				// TODO Auto-generated catch block
 180 
				// TODO Auto-generated catch block
149 
				e.printStackTrace();
 181 
				e.printStackTrace();
150 
			} catch (InventoryServiceException e) {
 182 
			} catch (InventoryServiceException e) {
151 
				// TODO Auto-generated catch block
 183 
				// TODO Auto-generated catch block
152 
				e.printStackTrace();
 184 
				e.printStackTrace();
153 
			}
 185 
			}
- 
 
 186
- 
 
 187 
			HSSFRow row=   sheet.createRow((short)iterator);
- 
 
 188 
			HSSFCell cell = row.createCell((short) 0);
- 
 
 189 
			cell.setCellValue(entry.getKey());
- 
 
 190 
			HSSFCellStyle style = hwb.createCellStyle();
- 
 
 191 
			style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
- 
 
 192 
			HSSFFont font = hwb.createFont();
- 
 
 193 
			style.setFillForegroundColor(HSSFColor.WHITE.index);
- 
 
 194 
			font.setColor(HSSFColor.BLACK.index);
- 
 
 195 
			style.setFont(font);
- 
 
 196 
			cell.setCellStyle(style);
- 
 
 197 
			cell.setCellValue(entry.getKey());
- 
 
 198 
			int count =1;
- 
 
 199 
			Double promotion_sale=0.0;
- 
 
 200 
			Double total_sale=0.0;
- 
 
 201 
			float avg_promotion_sale=0;
- 
 
 202 
			float avg_total_sale=0;
- 
 
 203 
			int total_sale_days=0;
- 
 
 204 
			int promotion_sale_days=0;
- 
 
 205 
			StringBuilder total_days_sale = new StringBuilder();
- 
 
 206 
			StringBuilder promotion_days_sale = new StringBuilder();
- 
 
 207 
			for(FbaSalesSnapshot sale:entry.getValue()){
- 
 
 208 
				if(sale.getPromotionOrderCount()!=-1){
- 
 
 209 
					promotion_days_sale.append(sale.getPromotionOrderCount());
- 
 
 210 
					avg_promotion_sale=avg_promotion_sale+sale.getPromotionOrderCount();
- 
 
 211 
					promotion_sale = promotion_sale+sale.getTotalPromotionSale();
- 
 
 212 
						promotion_sale_days++;
- 
 
 213 
				}
- 
 
 214 
				else{
- 
 
 215 
					promotion_days_sale.append("X");
- 
 
 216 
				}
- 
 
 217 
				if(count != entry.getValue().size()){
- 
 
 218 
					promotion_days_sale.append("-");
- 
 
 219 
				}
- 
 
 220 
				if(sale.getTotalOrderCount()!=-1){
- 
 
 221 
					total_days_sale.append(sale.getTotalOrderCount());
- 
 
 222 
					avg_total_sale=avg_total_sale+sale.getTotalOrderCount();
- 
 
 223 
					total_sale = total_sale+sale.getTotalSale();
- 
 
 224 
					total_sale_days++;
- 
 
 225 
				}
- 
 
 226 
				else{
- 
 
 227 
					total_days_sale.append("X");
- 
 
 228 
				}
- 
 
 229 
				if(count != entry.getValue().size()){
- 
 
 230 
					total_days_sale.append("-");
- 
 
 231 
				}
- 
 
 232 
				count++;
- 
 
 233 
			}
- 
 
 234 
			float promotion_sale_avg=0;
- 
 
 235 
			float total_sale_avg = 0;
- 
 
 236 
			if(promotion_sale_days!=0){
- 
 
 237 
				promotion_sale_avg = avg_promotion_sale/promotion_sale_days;
- 
 
 238 
			}
- 
 
 239 
			if(total_sale_days!=0){
- 
 
 240 
				total_sale_avg=avg_total_sale/total_sale_days;
- 
 
 241 
			}
154 
			AmazonFbaSalesSnapshot latest_snapshot = null;
 242 
			AmazonFbaSalesSnapshot latest_snapshot = null;
- 
 
 243 
			long availability=0;
- 
 
 244 
			double percent_diff=0;
155 
			try {
 245 
			try {
156 
				latest_snapshot = transactionClient.getAmazonFbaSalesLatestSnapshotForItem(entry.getKey());
 246 
				latest_snapshot = transactionClient.getAmazonFbaSalesLatestSnapshotForItem(entry.getKey());
- 
 
 247 
				availability = inventoryClient.getAmazonFbaItemInventory(entry.getKey());
157 
			} catch (TException e) {
 248 
			} catch (TException e) {
158 
				// TODO Auto-generated catch block
 249 
				// TODO Auto-generated catch block
159 
				e.printStackTrace();
 250 
				e.printStackTrace();
160 
			}
 251 
			}
- 
 
 252 
			double asp_total=0;
- 
 
 253 
			if(avg_total_sale!=0){
- 
 
 254 
				asp_total = total_sale/avg_total_sale;
- 
 
 255 
			}
161 
			int days_of_stock=0;
 256 
			int days_of_stock=0;
- 
 
 257 
			String days_of_stock_rule= new String();
162 
			if(latest_snapshot.getMinFbaPrice()==0 ||  latest_snapshot.getMinMfnPrice()==0){
 258 
			if(latest_snapshot.getMinFbaPrice()==0 ||  latest_snapshot.getMinMfnPrice()==0){
163 
				days_of_stock=0;
 259 
				days_of_stock=0;
- 
 
 260 
				days_of_stock_rule = "Min Prices not Available";
164 
			}
 261 
			}
165 
			else{
 262 
			else{
166 
				double percent_diff = getPercentageDifferenceFromMinimumPrice(latest_snapshot);
 263 
				percent_diff = getPercentageDifferenceFromMinimumPrice(latest_snapshot);
- 
 
 264 
				if(latest_snapshot.getSalePrice() <= latest_snapshot.getMinFbaPrice() &&  latest_snapshot.getSalePrice() <= latest_snapshot.getMinMfnPrice()){
- 
 
 265 
					if(percent_diff >= 0 && latest_snapshot.getSalePrice()<=asp_total){
167 
				if(percent_diff >= 3){
 266 
						days_of_stock = 15;
- 
 
 267 
						days_of_stock_rule = "Cheapest 15 days";
- 
 
 268 
						style.setFillForegroundColor(HSSFColor.GREEN.index);
- 
 
 269 
						///GREEN
- 
 
 270 
					}
- 
 
 271 
					else if(percent_diff >= 0 && latest_snapshot.getSalePrice()>asp_total){
168 
					days_of_stock = 15;
 272 
						days_of_stock = 7;
- 
 
 273 
						days_of_stock_rule = "Cheapest Price Increased 7 days";
- 
 
 274 
						style.setFillForegroundColor(HSSFColor.GREEN.index);
- 
 
 275 
						///GREEN
- 
 
 276 
					}
169 
				}
 277 
				}
- 
 
 278 
				else if(latest_snapshot.getSalePrice() > latest_snapshot.getMinFbaPrice() &&  latest_snapshot.getSalePrice() > latest_snapshot.getMinMfnPrice()){
170 
				else if(percent_diff > 0 && percent_diff < 3){
 279 
					if(percent_diff > -1 && percent_diff < 0){
- 
 
 280 
						days_of_stock = 4;
- 
 
 281 
						days_of_stock_rule = "Slightly Expensive (diff 0% to 1 %) 4 days";
- 
 
 282 
						style.setFillForegroundColor(HSSFColor.YELLOW.index);
- 
 
 283 
						///YELLOW
- 
 
 284 
					}
- 
 
 285 
					else{
- 
 
 286 
						days_of_stock = 0;
- 
 
 287 
						days_of_stock_rule = "Non Competitive 0 days";
- 
 
 288 
						style.setFillForegroundColor(HSSFColor.RED.index);
- 
 
 289 
						////RED
- 
 
 290 
					}
- 
 
 291 
				}else if(latest_snapshot.getSalePrice() < latest_snapshot.getMinFbaPrice() &&  latest_snapshot.getSalePrice() > latest_snapshot.getMinMfnPrice()){
- 
 
 292 
					if(((latest_snapshot.getMinFbaPrice() - latest_snapshot.getSalePrice())/latest_snapshot.getSalePrice())*100 >=0 && ((latest_snapshot.getMinMfnPrice() - latest_snapshot.getSalePrice())/latest_snapshot.getSalePrice())*100 <=-2){
- 
 
 293 
						days_of_stock = 4;
- 
 
 294 
						days_of_stock_rule = "Cheapest  AFN but not MFN 4 days";
- 
 
 295 
						style.setFillForegroundColor(HSSFColor.YELLOW.index);
- 
 
 296 
						////YELLOW
- 
 
 297 
					}
- 
 
 298 
					else{
171 
					days_of_stock = 10;
 299 
						days_of_stock = 0;
- 
 
 300 
						days_of_stock_rule = "Non Competitive 0 days";
- 
 
 301 
						style.setFillForegroundColor(HSSFColor.RED.index);
- 
 
 302 
						///RED
- 
 
 303 
					}
- 
 
 304 
				}
- 
 
 305 
			}
- 
 
 306 
 
- 
 
 307
- 
 
 308 
			rowhead.createCell((short) 23).setCellValue("VARIANCE-FROM-CHEAPEST");
- 
 
 309 
			rowhead.createCell((short) 24).setCellValue("DAYS-OF-STOCK-RULE");
- 
 
 310 
			row.createCell((short) 1).setCellValue(item.getBrand());
- 
 
 311 
			row.createCell((short) 2).setCellValue(category.getLabel());
- 
 
 312 
			row.createCell((short) 3).setCellValue(item.getProductGroup());
- 
 
 313 
			row.createCell((short) 4).setCellValue(item.getModelName()+" " + item.getModelNumber() + " " +item.getColor());
- 
 
 314 
			if(item.isIsWarehousePreferenceSticky()){
- 
 
 315 
				row.createCell((short) 5).setCellValue("YES");
- 
 
 316 
			}
- 
 
 317 
			else{
- 
 
 318 
				row.createCell((short) 5).setCellValue("NO");
- 
 
 319 
			}
- 
 
 320 
			if(vendor!=null){
- 
 
 321 
				row.createCell((short) 6).setCellValue(vendor.getName());
- 
 
 322 
			}
- 
 
 323 
			else{
- 
 
 324 
				row.createCell((short) 6).setCellValue("Preferred Vendor Not Set");
- 
 
 325 
			}
- 
 
 326 
			if(vip!=null){
- 
 
 327 
				row.createCell((short) 7).setCellValue(vip.getTransferPrice());
- 
 
 328 
				row.createCell((short) 8).setCellValue(vip.getNlc());
172 
				}
 329 
				}
- 
 
 330 
				else{
- 
 
 331 
					row.createCell((short) 7).setCellValue("Preferred Vendor Not Set");
173 
				else if(percent_diff > 0 && percent_diff < 1){
 332 
					row.createCell((short) 8).setCellValue("Preferred Vendor Not Set");
- 
 
 333 
			}
- 
 
 334 
			row.createCell((short) 9).setCellValue(total_days_sale.toString());
174 
					days_of_stock = 4;
 335 
			if(avg_total_sale!=0){
- 
 
 336
- 
 
 337 
				row.createCell((short) 10).setCellValue(String.format("%.2f", asp_total));
- 
 
 338 
			}
- 
 
 339 
			else{
- 
 
 340 
				row.createCell((short) 10).setCellValue(0);
- 
 
 341 
			}
- 
 
 342 
			row.createCell((short) 11).setCellValue(promotion_days_sale.toString());
- 
 
 343 
			if(avg_promotion_sale!=0){
- 
 
 344 
			double asp_promotion = promotion_sale/avg_promotion_sale;
- 
 
 345 
			row.createCell((short) 12).setCellValue(String.format("%.2f",asp_promotion));
- 
 
 346 
			}
- 
 
 347 
			else{
- 
 
 348 
				row.createCell((short) 12).setCellValue(0);
- 
 
 349 
			}
- 
 
 350 
			if(latest_snapshot.getMinFbaPrice()!=0){
- 
 
 351 
				row.createCell((short) 13).setCellValue(latest_snapshot.getMinFbaPrice());
- 
 
 352 
			}
- 
 
 353 
			else{
- 
 
 354 
				row.createCell((short) 13).setCellValue("Not Available");
- 
 
 355 
			}
- 
 
 356 
			if(latest_snapshot.getMinFbaPrice()!=0){
- 
 
 357 
				row.createCell((short) 14).setCellValue(latest_snapshot.getMinMfnPrice());
- 
 
 358 
			}
- 
 
 359 
			else{
- 
 
 360 
				row.createCell((short) 14).setCellValue("Not Available");
- 
 
 361 
			}
- 
 
 362
- 
 
 363 
			row.createCell((short) 15).setCellValue(latest_snapshot.getSalePrice());
- 
 
 364 
			long inv_mum=0;
- 
 
 365 
			if(allItemsAvailability.containsKey(entry.getKey())){
- 
 
 366 
				ItemInventory itemInventory = allItemsAvailability.get(entry.getKey());
- 
 
 367 
				Map<Long, Long> itemAvailability = itemInventory.getAvailability();
- 
 
 368 
				Map<Long, Long> itemReserved = itemInventory.getReserved();
- 
 
 369 
				Map<Long, Long> itemHeld = itemInventory.getHeld();
- 
 
 370 
				for (Map.Entry<Long,Long> itementry : itemAvailability.entrySet()) {
- 
 
 371 
					if(WarehouseIdsForMumbaiLocation.contains(itementry.getKey())){
- 
 
 372 
						System.out.println(itemAvailability.get(itementry.getKey()) - itemReserved.get(itementry.getKey()) - itemHeld.get(itementry.getKey()));
- 
 
 373 
						inv_mum =  inv_mum + itemAvailability.get(itementry.getKey()) - itemReserved.get(itementry.getKey()) - itemHeld.get(itementry.getKey());
- 
 
 374 
					}
175 
				}
 375 
				}
176 
				else if(latest_snapshot.getSalePrice() < latest_snapshot.getMinFbaPrice() &&  latest_snapshot.getSalePrice() > latest_snapshot.getMinMfnPrice() && percent_diff <= 2){
 - 
 
177 
					days_of_stock = 2;
 376 
				if(inv_mum!=0){
- 
 
 377 
					row.createCell((short) 16).setCellValue(inv_mum);
178 
				}
 378 
				}
179 
				else{
 379 
				else{
180 
					days_of_stock = 0;
 380 
					row.createCell((short) 16).setCellValue(0);
181 
				}
 381 
				}
182 
			}
 382 
			}
- 
 
 383 
			else{
- 
 
 384 
				row.createCell((short) 16).setCellValue(0);
- 
 
 385 
			}
- 
 
 386
- 
 
 387 
			row.createCell((short) 17).setCellValue(String.format("%.2f",total_sale_avg));
- 
 
 388
- 
 
 389 
			row.createCell((short) 18).setCellValue(days_of_stock);
- 
 
 390
- 
 
 391 
			int total_stock = (int) (total_sale_avg*days_of_stock);
- 
 
 392
- 
 
 393 
			row.createCell((short) 19).setCellValue(total_stock);
- 
 
 394
- 
 
 395 
			row.createCell((short) 20).setCellValue(availability);
- 
 
 396
- 
 
 397 
			long po = total_stock - availability;
- 
 
 398
- 
 
 399 
			row.createCell((short) 21).setCellValue(po);
- 
 
 400
- 
 
 401 
			if(item.getPreferredVendor()!=0){
- 
 
 402 
				double amount = total_sale_avg*days_of_stock*vip.getNlc();
- 
 
 403 
				row.createCell((short) 22).setCellValue(amount);
- 
 
 404 
			}
- 
 
 405 
			else{
- 
 
 406 
				row.createCell((short) 22).setCellValue(0.0);
- 
 
 407 
			}
- 
 
 408 
			row.createCell((short) 23).setCellValue(String.format("%.2f", percent_diff)+"%");
- 
 
 409
- 
 
 410 
			row.createCell((short) 24).setCellValue(days_of_stock_rule);
183 
 
 411 
 
184 
			HSSFRow row=   sheet.createRow((short)iterator);
 - 
 
185 
			row.createCell((short) 0).setCellValue(entry.getKey());
 - 
 
186 
			row.createCell((short) 1).setCellValue(days_sale.toString());
 - 
 
187 
			row.createCell((short) 2).setCellValue(item.getBrand() + " " + item.getModelName()+" " + item.getModelNumber() + " " +
 - 
 
188 
					item.getColor());
 - 
 
189 
			row.createCell((short) 3).setCellValue(vip.getTransferPrice());
 - 
 
190 
			row.createCell((short) 4).setCellValue(vip.getNlc());
 - 
 
191 
			if(item.isIsWarehousePreferenceSticky())
 - 
 
192 
				row.createCell((short) 5).setCellValue("YES");
 - 
 
193 
			else
 - 
 
194 
				row.createCell((short) 5).setCellValue("NO");
 - 
 
195 
			row.createCell((short) 6).setCellValue(vendor.getName());
 - 
 
196 
			row.createCell((short) 7).setCellValue(avg_sale*days_of_stock);
 - 
 
197 
			row.createCell((short) 8).setCellValue(latest_snapshot.getMinFbaPrice());
 - 
 
198 
			row.createCell((short) 9).setCellValue(latest_snapshot.getMinMfnPrice());
 - 
 
199 
			row.createCell((short) 10).setCellValue(latest_snapshot.getSalePrice());
 - 
 
200 
			double amount = avg_sale*days_of_stock*latest_snapshot.getSalePrice();
 - 
 
201 
			row.createCell((short) 11).setCellValue(amount);
 - 
 
202 
			row.createCell((short) 12).setCellValue(days_of_stock);
 - 
 
203 
			iterator++;
 412 
			iterator++;
204 
 
 413 
 
205 
		}
 414 
		}
206 
 
 415 
 
207 
		FileOutputStream fileOut = null;
 416 
		FileOutputStream fileOut = null;
Line 224... Line 433...
224 
			e.printStackTrace();
 433 
			e.printStackTrace();
225 
		}
 434 
		}
226 
 
 435 
 
227 
		String emailFromAddress = "build@shop2020.in";
 436 
		String emailFromAddress = "build@shop2020.in";
228 
		String password = "cafe@nes";
 437 
		String password = "cafe@nes";
229 
		String[] sendTo = new String[]{ "vikram.raghav@shop2020.in", "rajveer.singh@shop2020.in" };
 438 
		String[] sendTo = new String[]{ "vikram.raghav@shop2020.in", "rajveer.singh@shop2020.in","rajneesh.arora@shop2020.in" };
- 
 
 439 
		//String[] sendTo = new String[]{ "vikram.raghav@shop2020.in"};
230 
		String emailSubjectTxt = "FBA Stock Estimation Sheet";
 440 
		String emailSubjectTxt = "FBA Stock Estimation Sheet";
231 
 
 441 
 
232 
		try {
 442 
		try {
233 
			GmailUtils mailer = new GmailUtils();
 443 
			GmailUtils mailer = new GmailUtils();
234 
			mailer.sendSSLMessage(sendTo, emailSubjectTxt, "", emailFromAddress, password, "/home/FBA-Stock-File.xls");
 444 
			mailer.sendSSLMessage(sendTo, emailSubjectTxt, "", emailFromAddress, password, "/home/vikram/FBA-Stock-File.xls");
235 
		}
 445 
		}
236 
		catch (Exception ex) {
 446 
		catch (Exception ex) {
237 
			ex.printStackTrace();
 447 
			ex.printStackTrace();
238 
		}
 448 
		}
239 
 
 449