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