Subversion Repositories SmartDukaan

Rev

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