Subversion Repositories SmartDukaan

Rev

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