Subversion Repositories SmartDukaan

Rev

Rev 5504 | Rev 6165 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
5128 amit.gupta 1
package in.shop2020.catalog.util;
2
 
3
import in.shop2020.catalog.dashboard.shared.Item;
4
import in.shop2020.catalog.dashboard.shared.VendorPricings;
5
import in.shop2020.config.ConfigException;
5946 rajveer 6
import in.shop2020.model.v1.catalog.CatalogService;
5384 phani.kuma 7
import in.shop2020.model.v1.catalog.ItemType;
5128 amit.gupta 8
import in.shop2020.model.v1.catalog.status;
5946 rajveer 9
import in.shop2020.model.v1.inventory.InventoryService.Client;
5128 amit.gupta 10
import in.shop2020.thrift.clients.CatalogClient;
5946 rajveer 11
import in.shop2020.thrift.clients.InventoryClient;
5128 amit.gupta 12
import in.shop2020.thrift.clients.config.ConfigClient;
13
import in.shop2020.utils.CategoryManager;
14
import in.shop2020.utils.ConfigClientKeys;
15
 
16
import java.io.ByteArrayOutputStream;
17
import java.io.IOException;
18
import java.text.DecimalFormat;
19
import java.util.ArrayList;
20
import java.util.HashMap;
21
import java.util.List;
22
import java.util.Map;
23
 
24
import org.apache.log4j.Logger;
25
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
26
import org.apache.poi.hssf.util.HSSFColor;
27
import org.apache.poi.ss.usermodel.CellStyle;
28
import org.apache.poi.ss.usermodel.Font;
29
import org.apache.poi.ss.usermodel.Row;
30
import org.apache.poi.ss.usermodel.Sheet;
31
import org.apache.poi.ss.usermodel.Workbook;
32
 
33
public class ReportGenerator {
34
 
35
	private static Logger logger = Logger.getLogger(ReportGenerator.class);
36
	public static int TYPE_INACTIVE = 0;
37
	public static int TYPE_BREAKEVEN = 1;
38
	public static int TYPE_BOTH = 2;
39
	Map<String, String> configMap = getConfigdataforPriceCompare();
40
 
41
	public ByteArrayOutputStream generateExcelStream(int type) {
42
		ByteArrayOutputStream baosXLS = new ByteArrayOutputStream();
43
 
44
		DecimalFormat numberFormat = new DecimalFormat("#.##");
45
 
46
		Workbook wb = new HSSFWorkbook();
47
 
48
		Font font = wb.createFont();
49
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
50
		CellStyle styleBold = wb.createCellStyle();
51
		styleBold.setFont(font);
52
 
53
		CellStyle styleRedBG = wb.createCellStyle();
54
		styleRedBG.setFont(font);
55
		styleRedBG.setFillBackgroundColor(HSSFColor.RED.index);
56
 
57
		CellStyle styleWT = wb.createCellStyle();
58
		styleWT.setWrapText(true);
59
		short rowNo = 0;
60
 
61
        List<in.shop2020.model.v1.catalog.Item> thriftItemList = new ArrayList<in.shop2020.model.v1.catalog.Item>();
62
        List<in.shop2020.model.v1.catalog.Item> thriftItemListInactive = new ArrayList<in.shop2020.model.v1.catalog.Item>();
63
        try {
64
            CatalogClient catalogServiceClient = new CatalogClient(ConfigClientKeys.catalog_service_server_host_prod.toString(),
65
                    ConfigClientKeys.catalog_service_server_port.toString());
5946 rajveer 66
            CatalogService.Client catalogClient = catalogServiceClient.getClient();
67
 
68
            InventoryClient inventoryServiceClient = new InventoryClient();
69
            Client inventoryClient = inventoryServiceClient.getClient();
70
 
5128 amit.gupta 71
            thriftItemListInactive.addAll(catalogClient.getAllItemsByStatus(status.PAUSED));
72
            if(TYPE_INACTIVE != type){
5183 amit.gupta 73
            	catalogServiceClient = new CatalogClient(ConfigClientKeys.catalog_service_server_host_prod.toString(),
74
                        ConfigClientKeys.catalog_service_server_port.toString());
75
            	catalogClient = catalogServiceClient.getClient();
5128 amit.gupta 76
	            thriftItemList.addAll(catalogClient.getAllItemsByStatus(status.ACTIVE));
5183 amit.gupta 77
 
78
	            catalogServiceClient = new CatalogClient(ConfigClientKeys.catalog_service_server_host_prod.toString(),
79
                        ConfigClientKeys.catalog_service_server_port.toString());
80
            	catalogClient = catalogServiceClient.getClient();
5128 amit.gupta 81
	            thriftItemList.addAll(catalogClient.getAllItemsByStatus(status.PAUSED_BY_RISK));
82
	            thriftItemList.addAll(thriftItemListInactive);
83
	            Sheet ngpSheet = wb.createSheet("Negative Gross Profit");
84
	            rowNo = 0;
85
 
86
	            Row headerRow = ngpSheet.createRow(rowNo);
87
	            headerRow.createCell(0).setCellValue("Item Id");
88
	            headerRow.getCell(0).setCellStyle(styleBold);
89
	            headerRow.createCell(1).setCellValue("Brand");
90
	            headerRow.getCell(1).setCellStyle(styleBold);
91
	            headerRow.createCell(2).setCellValue("Model Name");
92
	            headerRow.getCell(2).setCellStyle(styleBold);
93
	            headerRow.createCell(3).setCellValue("Model Number");
94
	            headerRow.getCell(3).setCellStyle(styleBold);
95
	            headerRow.createCell(4).setCellValue("Color");
96
	            headerRow.getCell(4).setCellStyle(styleBold);
97
	            headerRow.createCell(5).setCellValue("Selling Price");
98
	            headerRow.getCell(5).setCellStyle(styleBold);
99
	            headerRow.createCell(6).setCellValue("Break Even Price");
100
	            headerRow.getCell(6).setCellStyle(styleBold);
101
	            headerRow.createCell(7).setCellValue("Current Status");
102
	            headerRow.getCell(7).setCellStyle(styleBold);
103
	            headerRow.createCell(8).setCellValue("Reason");
104
	            headerRow.getCell(8).setCellStyle(styleBold);
105
	            for(in.shop2020.model.v1.catalog.Item thriftItem : thriftItemList) {
5946 rajveer 106
	            	List<in.shop2020.model.v1.inventory.VendorItemPricing> vip = inventoryClient.getAllItemPricing(thriftItem.getId());
5128 amit.gupta 107
	            	Item item = getItemFromThriftItem(thriftItem, vip);
108
	            	List<Object> breakeven = isBreakeven(item);
109
	            	if(!(Boolean)breakeven.get(0)){
110
	            		try{
111
	            		Row itemRow = ngpSheet.createRow(++rowNo);
112
	            		itemRow.createCell(0).setCellValue(item.getId());
113
	            		itemRow.createCell(1).setCellValue(item.getBrand());
114
	            		itemRow.createCell(2).setCellValue(item.getModelName());
115
	            		itemRow.createCell(3).setCellValue(item.getModelNumber());
116
	            		itemRow.createCell(4).setCellValue(item.getColor());
117
 
118
	            		Double sellingPrice = item.getSellingPrice();
119
	            		if(sellingPrice != null){
120
	            			itemRow.createCell(5).setCellValue(numberFormat.format(sellingPrice));
121
	            		} else {
122
	            			itemRow.createCell(5).setCellValue("NULL");
123
	            		}
124
 
125
	            		try{
126
	            			itemRow.createCell(6).setCellValue(numberFormat.format((Double)breakeven.get(1)));
127
	            		} catch (Exception e){
128
	            			itemRow.createCell(6).setCellValue("Empty");
129
	            		}
130
	            		itemRow.createCell(7).setCellValue(thriftItem.getItemStatus().toString());
131
	            		try { 
132
	            			itemRow.createCell(8).setCellValue((String)breakeven.get(1));
133
	            		}catch (Exception e) {
134
	            			itemRow.createCell(8).setCellValue("");							
135
						}
136
	            		}catch (Exception e) {
137
							logger.info("Error occurred for item : " + item.getId());
138
							System.out.println("Item id failed for breakeven report: " + item.getId());
139
						}
140
	            	}
141
	            }
142
            }
143
 
144
 
145
            if(TYPE_BREAKEVEN != type){
5183 amit.gupta 146
            	catalogServiceClient = new CatalogClient(ConfigClientKeys.catalog_service_server_host_prod.toString(),
147
                         ConfigClientKeys.catalog_service_server_port.toString());
148
             	catalogClient = catalogServiceClient.getClient();
149
 
5128 amit.gupta 150
            	thriftItemListInactive.addAll(catalogClient.getAllItemsByStatus(status.PHASED_OUT));
151
            	Sheet inactiveItemsSheet = wb.createSheet("Inactive Items with Positive Inventory");
152
	            rowNo = 0;
153
	            Row anotherHeaderRow = inactiveItemsSheet.createRow(0);
154
	            anotherHeaderRow.createCell(0).setCellValue("Item Id");
155
	            anotherHeaderRow.getCell(0).setCellStyle(styleBold);
156
	            anotherHeaderRow.createCell(1).setCellValue("Brand");
157
	            anotherHeaderRow.getCell(1).setCellStyle(styleBold);
158
	            anotherHeaderRow.createCell(2).setCellValue("Model Name");
159
	            anotherHeaderRow.getCell(2).setCellStyle(styleBold);
160
	            anotherHeaderRow.createCell(3).setCellValue("Model Number");
161
	            anotherHeaderRow.getCell(3).setCellStyle(styleBold);
162
	            anotherHeaderRow.createCell(4).setCellValue("Color");
163
	            anotherHeaderRow.getCell(4).setCellStyle(styleBold);
164
	            anotherHeaderRow.createCell(5).setCellValue("Status");
165
	            anotherHeaderRow.getCell(5).setCellStyle(styleBold);
166
	            anotherHeaderRow.createCell(6).setCellValue("Available Pieces");
167
	            anotherHeaderRow.getCell(6).setCellStyle(styleBold);
168
				for (in.shop2020.model.v1.catalog.Item thriftItem : thriftItemListInactive) {
169
					try {
5946 rajveer 170
						in.shop2020.model.v1.inventory.ItemInventory itemInventory = inventoryClient.getItemInventoryByItemId(thriftItem.getId());
5128 amit.gupta 171
						long available = 0;
172
						for(long count : itemInventory.getAvailability().values()){
173
							available += count;
174
						}
175
						for(long count : itemInventory.getReserved().values()){
176
							available -= count;
177
						}
178
						if(available > 0){
179
							Row itemRow = inactiveItemsSheet.createRow(++rowNo);
180
		                	itemRow.createCell(0).setCellValue(thriftItem.getId());
181
		                	itemRow.createCell(1).setCellValue(thriftItem.getBrand());
182
		                	itemRow.createCell(2).setCellValue(thriftItem.getModelName());
183
		                	itemRow.createCell(3).setCellValue(thriftItem.getModelNumber());
184
		                	itemRow.createCell(4).setCellValue(thriftItem.getColor());
185
		                	itemRow.createCell(5).setCellValue(thriftItem.getItemStatus().toString());
186
		                	itemRow.createCell(6).setCellValue(available);
187
						}
188
					} catch (Exception e) {
189
						logger.error("Error getting live inventory for item" + thriftItem.getId() + "\n"
190
								+ e);
191
					}
192
				}
193
            }
194
            try {
195
            	wb.write(baosXLS);
196
            	baosXLS.close();
197
            } catch (IOException e) {
198
            	e.printStackTrace();
199
            	throw e;
200
            }
201
 
202
        } catch (Exception e) {
203
        	e.printStackTrace();
204
        	baosXLS = null;
205
            logger.error("Error getting items from Catalog\n" + e);
206
        }
207
 
208
 
209
		return baosXLS;
210
	}
211
 
212
	private List<Object> isBreakeven(Item newItem) {
213
		List<Object> list = new ArrayList<Object>();
214
		boolean message = true;
215
 
216
		if (newItem.getSellingPrice() == null || newItem.getSellingPrice().compareTo(0d) <= 0) {
217
			message=false;
218
			list.add(message);
219
			list.add("Selling price is empty or 0");
220
			return list;
221
		}
222
 
223
		double transferPrice;
224
		if (newItem.getPreferredVendor() == null
225
				&& !newItem.getVendorPricesMap().isEmpty()) {
226
			transferPrice = -1;
227
			for (VendorPricings vendorDetail : newItem.getVendorPricesMap()
228
					.values()) {
229
				if (transferPrice > vendorDetail.getTransferPrice()
230
						|| transferPrice == -1) {
231
					transferPrice = vendorDetail.getTransferPrice();
232
				}
233
			}
234
		} else if (!newItem.getVendorPricesMap().isEmpty()
235
				&& newItem.getVendorPricesMap().containsKey(
236
						newItem.getPreferredVendor())) {
237
			transferPrice = newItem.getVendorPricesMap()
238
					.get(newItem.getPreferredVendor()).getTransferPrice();
239
		} else {
240
			message = false;
241
			list.add(message);
242
			list.add("Preferred vendor is not in Vendor Item Mapping.");
243
			return list;
244
		}
245
 
246
		double breakeven;
247
 
248
		//if weight is not mention add courier cost as Rs. 60
249
		if (newItem.getWeight() == null) {
250
			breakeven = transferPrice + 60;
251
		} else {
252
 
253
			double weightfactor = Math.ceil((newItem.getWeight() * 1000)
254
					/ Double.parseDouble(configMap.get("courier_weight_factor")));
255
			double couriercost = Double.parseDouble(configMap
256
					.get("courier_cost_factor")) * weightfactor;
257
			double costfactor = (Double.parseDouble(configMap
258
					.get("transfer_price_percentage")) * transferPrice) / 100;
259
			if (costfactor < Double.parseDouble(configMap
260
					.get("transfer_price_factor"))) {
261
				breakeven = transferPrice
262
						+ couriercost
263
						+ Double.parseDouble(configMap
264
								.get("breakeven_additon_factor"));
265
			} else {
266
				breakeven = (transferPrice + couriercost)
267
						/ Double.parseDouble(configMap.get("breakeven_divisor"));
268
			}
269
		}
270
 
271
		if (Double.compare(breakeven, newItem.getSellingPrice())>=0) {
272
			message=false;
273
			list.add(message);
274
			list.add(breakeven);
275
			return list;
276
		}
277
		list.add(message);
278
		return list;
279
	}
280
 
281
	private Map<String, String> getConfigdataforPriceCompare() {
282
		Map<String, String> ConfigMap = new HashMap<String, String>();
283
		try {
284
			ConfigMap.put(
285
					"courier_cost_factor",
286
					ConfigClient.getClient().get(
287
							ConfigClientKeys.courier_cost_factor.toString()));
288
			ConfigMap.put("courier_weight_factor", ConfigClient.getClient()
289
					.get(ConfigClientKeys.courier_weight_factor.toString()));
290
			ConfigMap.put(
291
					"breakeven_divisor",
292
					ConfigClient.getClient().get(
293
							ConfigClientKeys.breakeven_divisor.toString()));
294
			ConfigMap.put("breakeven_additon_factor", ConfigClient.getClient()
295
					.get(ConfigClientKeys.breakeven_additon_factor.toString()));
296
			ConfigMap
297
					.put("transfer_price_percentage",
298
							ConfigClient.getClient().get(
299
									ConfigClientKeys.transfer_price_percentage
300
											.toString()));
301
			ConfigMap.put("transfer_price_factor", ConfigClient.getClient()
302
					.get(ConfigClientKeys.transfer_price_factor.toString()));
303
		} catch (ConfigException ce) {
304
			logger.error(
305
					"Unable to connect to the config server. Setting sensible defaults.",
306
					ce);
307
			ConfigMap.put("courier_cost_factor", "60");
308
			ConfigMap.put("courier_weight_factor", "500");
309
			ConfigMap.put("breakeven_divisor", "0.98");
310
			ConfigMap.put("breakeven_additon_factor", "50");
311
			ConfigMap.put("transfer_price_percentage", "2");
312
			ConfigMap.put("transfer_price_factor", "50");
313
		}
314
		return ConfigMap;
315
	}
316
 
317
	/**
318
	 * Creates a new Item object and populates its attributes from thrift item
319
	 * passed as parameter. Also creates a Map each for VendorItemPricing,
320
	 * VendorItemMapping, SourceItemPricing and SimilarItems and adds these maps
321
	 * to the new Item object.
322
	 * 
323
	 * @param thriftItem
324
	 * @param tVendorPricings
325
	 * @param tVendorMappings
326
	 * @param tSourceMappings
327
	 * @param tSimilarItems
328
	 * @return item object with attributes copied from thrift item object.
329
	 */
330
	private Item getItemFromThriftItem(
331
			in.shop2020.model.v1.catalog.Item thriftItem,
5946 rajveer 332
			List<in.shop2020.model.v1.inventory.VendorItemPricing> tVendorPricings) {
5128 amit.gupta 333
 
334
		Map<Long, VendorPricings> vendorPricingMap = new HashMap<Long, VendorPricings>();
335
		VendorPricings vPricings;
336
		if (tVendorPricings != null) {
5946 rajveer 337
			for (in.shop2020.model.v1.inventory.VendorItemPricing vip : tVendorPricings) {
5128 amit.gupta 338
				vPricings = new VendorPricings();
339
				vPricings.setVendorId(vip.getVendorId());
340
				vPricings.setMop(vip.getMop());
341
				vPricings.setDealerPrice(vip.getDealerPrice());
342
				vPricings.setTransferPrice(vip.getTransferPrice());
343
				vendorPricingMap.put(vPricings.getVendorId(), vPricings);
344
			}
345
		}
346
 
347
 
348
		Item item = new Item(thriftItem.getId(), thriftItem.getProductGroup(),
349
				thriftItem.getBrand(), thriftItem.getModelNumber(),
350
				thriftItem.getModelName(), thriftItem.getColor(),
351
				CategoryManager.getCategoryManager().getCategoryLabel(
352
						thriftItem.getCategory()), thriftItem.getCategory(),
353
				thriftItem.getComments(), thriftItem.getCatalogItemId(),
354
				thriftItem.getFeatureId(), thriftItem.getFeatureDescription(),
355
				thriftItem.isSetMrp() ? thriftItem.getMrp() : null,
356
				thriftItem.isSetSellingPrice() ? thriftItem.getSellingPrice()
357
						: null,
358
				thriftItem.isSetWeight() ? thriftItem.getWeight() : null,
359
				thriftItem.getAddedOn(), thriftItem.getStartDate(),
5219 amit.gupta 360
				thriftItem.getComingSoonStartDate(), thriftItem.getExpectedArrivalDate(),
5128 amit.gupta 361
				thriftItem.getRetireDate(), thriftItem.getUpdatedOn(),
362
				thriftItem.getItemStatus().name(), thriftItem.getItemStatus()
363
						.getValue(), thriftItem.getStatus_description(),
5309 rajveer 364
				thriftItem.getBestDealText(),
5128 amit.gupta 365
				thriftItem.isSetBestDealValue() ? thriftItem.getBestDealValue()
366
						: null,
367
				thriftItem.isSetBestSellingRank() ? thriftItem
368
						.getBestSellingRank() : null,
369
				thriftItem.isDefaultForEntity(), thriftItem.isRisky(),
370
				thriftItem.isSetExpectedDelay() ? thriftItem.getExpectedDelay()
371
						: null,
372
				thriftItem.isIsWarehousePreferenceSticky(),
5384 phani.kuma 373
				thriftItem.isHasItemNo(),
374
                ItemType.SERIALIZED.equals(thriftItem.getType()),
5460 phani.kuma 375
                thriftItem.isClearance(),
5128 amit.gupta 376
				thriftItem.isSetPreferredVendor() ? thriftItem
377
						.getPreferredVendor() : null, null,
5504 phani.kuma 378
				vendorPricingMap, null, null, null, null);
5128 amit.gupta 379
		return item;
380
	}
381
 
382
}