| 36624 |
amit |
1 |
package com.spice.profitmandi.common.util;
|
|
|
2 |
|
|
|
3 |
import com.spice.profitmandi.common.exception.ProfitMandiBusinessException;
|
|
|
4 |
import com.spice.profitmandi.common.model.*;
|
| 37126 |
amit |
5 |
import com.spice.profitmandi.dao.model.PasswordOverrideRow;
|
| 36624 |
amit |
6 |
import in.shop2020.model.v1.order.WalletReferenceType;
|
|
|
7 |
import org.apache.logging.log4j.LogManager;
|
|
|
8 |
import org.apache.logging.log4j.Logger;
|
|
|
9 |
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
|
|
|
10 |
import org.apache.poi.ss.usermodel.*;
|
|
|
11 |
import org.apache.poi.ss.usermodel.Row.MissingCellPolicy;
|
|
|
12 |
import org.apache.poi.ss.util.CellRangeAddress;
|
|
|
13 |
import org.apache.poi.xssf.streaming.SXSSFSheet;
|
|
|
14 |
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
|
|
|
15 |
import org.apache.poi.xssf.usermodel.XSSFRow;
|
|
|
16 |
import org.apache.poi.xssf.usermodel.XSSFSheet;
|
|
|
17 |
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
|
|
|
18 |
|
|
|
19 |
import java.io.IOException;
|
|
|
20 |
import java.io.InputStream;
|
|
|
21 |
import java.io.OutputStream;
|
|
|
22 |
import java.time.LocalDateTime;
|
|
|
23 |
import java.time.ZoneId;
|
|
|
24 |
import java.util.*;
|
|
|
25 |
import java.util.stream.Collectors;
|
|
|
26 |
|
|
|
27 |
public class ExcelUtils {
|
|
|
28 |
private static final String TAG_ID = "Tag Id";
|
|
|
29 |
private static final String TAG_LABEL = "Tag Label";
|
|
|
30 |
private static final String ITEM_ID = "Item Id";
|
|
|
31 |
private static final String BRAND = "Brand";
|
|
|
32 |
private static final String MODEL_NAME = "Model Name";
|
|
|
33 |
private static final String MODEL_NUMBER = "Model Number";
|
|
|
34 |
private static final String COLOR = "Color";
|
|
|
35 |
private static final String SELLING_PRICE = "Selling Price";
|
|
|
36 |
private static final String MOP = "MOP";
|
|
|
37 |
private static final String SUPPORT_PRICE = "Support Price";
|
|
|
38 |
private static final String START_DATE = "Start Date";
|
|
|
39 |
private static final String TAG_LISTING = "Tag Listing";
|
|
|
40 |
private static final String FOFO_ID = "fofoId";
|
|
|
41 |
private static final String STORE_NAME = "storeName";
|
|
|
42 |
private static final String EMAIL = "email";
|
|
|
43 |
private static final String TARGET_VALUE = "targetValue";
|
|
|
44 |
|
|
|
45 |
private static List<Character> ALPHABETS = "ABCDEFGHIJKLMNOPQRSTUVWXYZ".chars().mapToObj(c -> (char) c).collect(Collectors.toList());
|
|
|
46 |
|
|
|
47 |
|
|
|
48 |
private static final List<WalletReferenceType> BULK_WALLET_REFERENCES = Arrays.asList(
|
|
|
49 |
WalletReferenceType.SCHEME_OUT, WalletReferenceType.ADDITIONAL_SCHEME,
|
|
|
50 |
WalletReferenceType.BRAND_FEE, WalletReferenceType.FESTIVE_OFFER, WalletReferenceType.GOODWILL_GESTURE,
|
|
|
51 |
WalletReferenceType.EOL, WalletReferenceType.ACTIVATION_SCHEME, WalletReferenceType.BRAND_PAYOUT,
|
|
|
52 |
WalletReferenceType.SALES_MARKETING_SUPPORT, WalletReferenceType.INVESTMENT_PAYOUT,
|
|
|
53 |
WalletReferenceType.CATEGORY_SPL_PAYOUT, WalletReferenceType.REFERRAL_INCENTIVES, WalletReferenceType.OTHERS,
|
|
|
54 |
WalletReferenceType.SHOP_BOY_SUPPORT, WalletReferenceType.PREBOOKING_ORDER
|
|
|
55 |
|
|
|
56 |
);
|
|
|
57 |
|
|
|
58 |
private static final Logger LOGGER = LogManager.getLogger(ExcelUtils.class);
|
|
|
59 |
|
|
|
60 |
public static void main(String[] args) throws Throwable {
|
|
|
61 |
// List<Integer> intervals = Arrays.asList(5, 10, 15, 20, 25);
|
|
|
62 |
// writeInventoryItemAgingModels(inventoryItemAgingModels, intervals);
|
|
|
63 |
}
|
|
|
64 |
|
| 37126 |
amit |
65 |
// Dev/staging password-override bulk sheet: Type | Id | Identifier | Password
|
|
|
66 |
public static void writePasswordOverrides(List<PasswordOverrideRow> rows, OutputStream outputStream)
|
|
|
67 |
throws IOException {
|
|
|
68 |
SXSSFWorkbook workbook = new SXSSFWorkbook();
|
|
|
69 |
SXSSFSheet sheet = workbook.createSheet("PasswordOverrides");
|
|
|
70 |
String[] headers = {"Type", "Id", "Identifier", "Password"};
|
|
|
71 |
Row header = sheet.createRow(0);
|
|
|
72 |
for (int c = 0; c < headers.length; c++) {
|
|
|
73 |
header.createCell(c).setCellValue(headers[c]);
|
|
|
74 |
}
|
|
|
75 |
int rowIndex = 1;
|
|
|
76 |
for (PasswordOverrideRow r : rows) {
|
|
|
77 |
Row row = sheet.createRow(rowIndex++);
|
|
|
78 |
row.createCell(0).setCellValue(r.getType());
|
|
|
79 |
row.createCell(1).setCellValue("PARTNER".equalsIgnoreCase(r.getType()) ? r.getRetailerId() : r.getAuthUserId());
|
|
|
80 |
row.createCell(2).setCellValue(r.getIdentifier() == null ? "" : r.getIdentifier());
|
|
|
81 |
row.createCell(3).setCellValue(r.getCleartextPassword() == null ? "" : r.getCleartextPassword());
|
|
|
82 |
}
|
|
|
83 |
workbook.write(outputStream);
|
|
|
84 |
workbook.dispose();
|
|
|
85 |
}
|
|
|
86 |
|
|
|
87 |
public static List<PasswordOverrideRow> parsePasswordOverrides(InputStream inputStream) throws Exception {
|
|
|
88 |
List<PasswordOverrideRow> result = new ArrayList<>();
|
|
|
89 |
XSSFWorkbook workBook = new XSSFWorkbook(inputStream);
|
|
|
90 |
try {
|
|
|
91 |
workBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
|
|
|
92 |
XSSFSheet sheet = workBook.getSheetAt(0);
|
|
|
93 |
for (int rowNumber = 1; rowNumber <= sheet.getLastRowNum(); rowNumber++) {
|
|
|
94 |
XSSFRow row = sheet.getRow(rowNumber);
|
|
|
95 |
if (row == null) {
|
|
|
96 |
continue;
|
|
|
97 |
}
|
|
|
98 |
String type = cellString(row.getCell(0));
|
|
|
99 |
String idStr = cellString(row.getCell(1));
|
|
|
100 |
String identifier = cellString(row.getCell(2));
|
|
|
101 |
String password = cellString(row.getCell(3));
|
|
|
102 |
if ((type == null || type.trim().isEmpty()) && (idStr == null || idStr.trim().isEmpty())
|
|
|
103 |
&& (identifier == null || identifier.trim().isEmpty())
|
|
|
104 |
&& (password == null || password.trim().isEmpty())) {
|
|
|
105 |
continue; // skip fully blank rows
|
|
|
106 |
}
|
|
|
107 |
PasswordOverrideRow r = new PasswordOverrideRow();
|
|
|
108 |
r.setType(type == null ? "" : type.trim());
|
|
|
109 |
int id = 0;
|
|
|
110 |
if (idStr != null && !idStr.trim().isEmpty()) {
|
|
|
111 |
id = (int) Double.parseDouble(idStr.trim());
|
|
|
112 |
}
|
|
|
113 |
if ("PARTNER".equalsIgnoreCase(r.getType())) {
|
|
|
114 |
r.setRetailerId(id);
|
|
|
115 |
} else {
|
|
|
116 |
r.setAuthUserId(id);
|
|
|
117 |
}
|
|
|
118 |
r.setIdentifier(identifier == null ? "" : identifier.trim());
|
|
|
119 |
r.setCleartextPassword(password == null ? "" : password.trim());
|
|
|
120 |
result.add(r);
|
|
|
121 |
}
|
|
|
122 |
} finally {
|
|
|
123 |
workBook.close();
|
|
|
124 |
}
|
|
|
125 |
return result;
|
|
|
126 |
}
|
|
|
127 |
|
|
|
128 |
private static String cellString(Cell cell) {
|
|
|
129 |
if (cell == null) {
|
|
|
130 |
return null;
|
|
|
131 |
}
|
|
|
132 |
if (cell.getCellTypeEnum() == CellType.NUMERIC) {
|
|
|
133 |
double d = cell.getNumericCellValue();
|
|
|
134 |
if (d == Math.floor(d) && !Double.isInfinite(d)) {
|
|
|
135 |
return String.valueOf((long) d);
|
|
|
136 |
}
|
|
|
137 |
return String.valueOf(d);
|
|
|
138 |
}
|
|
|
139 |
return cell.toString();
|
|
|
140 |
}
|
|
|
141 |
|
| 36624 |
amit |
142 |
public static List<TagListingModel> parse(InputStream inputStream) throws Exception {
|
|
|
143 |
|
|
|
144 |
List<TagListingModel> tagListings = new ArrayList<>();
|
|
|
145 |
XSSFWorkbook myWorkBook = null;
|
|
|
146 |
try {
|
|
|
147 |
// FileInputStream fileInputStream = new
|
|
|
148 |
// FileInputStream("/home/ashikali/tag_listing1.xlsx");
|
|
|
149 |
myWorkBook = new XSSFWorkbook(inputStream);
|
|
|
150 |
|
|
|
151 |
myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
|
|
|
152 |
// Return first sheet from the XLSX workbook
|
|
|
153 |
XSSFSheet mySheet = myWorkBook.getSheetAt(0);
|
|
|
154 |
LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
|
|
|
155 |
|
|
|
156 |
for (int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++) {
|
|
|
157 |
XSSFRow row = mySheet.getRow(rowNumber);
|
|
|
158 |
LOGGER.info("row {}", row);
|
|
|
159 |
TagListingModel tagListing = new TagListingModel();
|
|
|
160 |
if (row.getCell(0) != null && row.getCell(0).getCellTypeEnum() == CellType.NUMERIC) {
|
|
|
161 |
tagListing.setTagId((Double.valueOf(row.getCell(0).getNumericCellValue())).intValue());
|
|
|
162 |
} else {
|
|
|
163 |
ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(TAG_ID,
|
|
|
164 |
row.getCell(0).toString(), "TGLSTNG_VE_1010");
|
|
|
165 |
LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
|
|
|
166 |
throw profitMandiBusinessException;
|
|
|
167 |
}
|
|
|
168 |
|
|
|
169 |
if (row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.NUMERIC) {
|
|
|
170 |
tagListing.setItemId(Double.valueOf(row.getCell(2).toString()).intValue());
|
|
|
171 |
} else {
|
|
|
172 |
ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
|
|
|
173 |
ITEM_ID, row.getCell(2).toString(), "TGLSTNG_VE_1010");
|
|
|
174 |
LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
|
|
|
175 |
throw profitMandiBusinessException;
|
|
|
176 |
}
|
|
|
177 |
|
|
|
178 |
if (row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC) {
|
|
|
179 |
tagListing.setSellingPrice(Double.valueOf(row.getCell(7).toString()).floatValue());
|
|
|
180 |
} else {
|
|
|
181 |
ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
|
|
|
182 |
SELLING_PRICE, row.getCell(7), "TGLSTNG_VE_1010");
|
|
|
183 |
LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
|
|
|
184 |
throw profitMandiBusinessException;
|
|
|
185 |
}
|
|
|
186 |
if (row.getCell(8) != null && row.getCell(8).getCellTypeEnum() == CellType.NUMERIC) {
|
|
|
187 |
tagListing.setMop(Double.valueOf(row.getCell(8).toString()).floatValue());
|
|
|
188 |
} else {
|
|
|
189 |
ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(MOP,
|
|
|
190 |
row.getCell(8), "TGLSTNG_VE_1010");
|
|
|
191 |
LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
|
|
|
192 |
throw profitMandiBusinessException;
|
|
|
193 |
}
|
|
|
194 |
if (row.getCell(9) != null && row.getCell(9).getCellTypeEnum() == CellType.NUMERIC) {
|
|
|
195 |
tagListing.setSupportPrice(Double.valueOf(row.getCell(9).toString()).floatValue());
|
|
|
196 |
} else {
|
|
|
197 |
ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
|
|
|
198 |
SUPPORT_PRICE, row.getCell(9).toString(), "TGLSTNG_VE_1010");
|
|
|
199 |
LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
|
|
|
200 |
throw profitMandiBusinessException;
|
|
|
201 |
}
|
|
|
202 |
if (row.getCell(10) != null && row.getCell(10).getCellTypeEnum() == CellType.NUMERIC) {
|
|
|
203 |
tagListing.setMaxDiscountPrice(Double.valueOf(row.getCell(10).toString()).floatValue());
|
|
|
204 |
} else {
|
|
|
205 |
ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
|
|
|
206 |
SUPPORT_PRICE, row.getCell(10).toString(), "TGLSTNG_VE_1010");
|
|
|
207 |
LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
|
|
|
208 |
throw profitMandiBusinessException;
|
|
|
209 |
}
|
|
|
210 |
if (row.getCell(11) != null && HSSFDateUtil.isCellDateFormatted(row.getCell(11))) {
|
|
|
211 |
Date date = row.getCell(11).getDateCellValue();
|
|
|
212 |
LocalDateTime startDate = LocalDateTime.ofInstant(date.toInstant(), ZoneId.systemDefault());
|
|
|
213 |
tagListing.setStartDate(startDate);
|
|
|
214 |
} else {
|
|
|
215 |
ProfitMandiBusinessException profitMandiBusinessException = new ProfitMandiBusinessException(
|
|
|
216 |
START_DATE, row.getCell(11).toString(), "TGLSTNG_VE_1010");
|
|
|
217 |
LOGGER.error("Excel file parse error : ", profitMandiBusinessException);
|
|
|
218 |
throw profitMandiBusinessException;
|
|
|
219 |
}
|
|
|
220 |
tagListings.add(tagListing);
|
|
|
221 |
}
|
|
|
222 |
myWorkBook.close();
|
|
|
223 |
} catch (IOException ioException) {
|
|
|
224 |
ioException.printStackTrace();
|
|
|
225 |
throw new ProfitMandiBusinessException(ProfitMandiConstants.EXCEL_FILE, ioException.getMessage(),
|
|
|
226 |
"EXL_VE_1000");
|
|
|
227 |
} finally {
|
|
|
228 |
if (myWorkBook != null) {
|
|
|
229 |
try {
|
|
|
230 |
myWorkBook.close();
|
|
|
231 |
} catch (IOException e) {
|
|
|
232 |
// TODO Auto-generated catch block
|
|
|
233 |
e.printStackTrace();
|
|
|
234 |
}
|
|
|
235 |
}
|
|
|
236 |
}
|
|
|
237 |
return tagListings;
|
|
|
238 |
}
|
|
|
239 |
|
|
|
240 |
public static void writeInventoryItemAgingModels(List<InventoryItemAgingModel> inventoryItemAgingModels,
|
|
|
241 |
List<Integer> intervals, OutputStream outputStream) {
|
|
|
242 |
SXSSFWorkbook workbook = new SXSSFWorkbook();
|
|
|
243 |
|
|
|
244 |
// CreationHelper createHelper = workbook.getCreationHelper();
|
|
|
245 |
boolean showPartner = inventoryItemAgingModels.get(0).getFofoId() > 0;
|
|
|
246 |
int nonValueColumns = showPartner ? 9 : 6;
|
|
|
247 |
SXSSFSheet sheet = workbook.createSheet("InventoryItemAging");
|
|
|
248 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
|
|
|
249 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
|
|
|
250 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
|
|
|
251 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
|
|
|
252 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
|
|
|
253 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
|
|
|
254 |
|
|
|
255 |
int rowIndex = 0;
|
|
|
256 |
Row rowHeader = sheet.createRow(rowIndex++);
|
|
|
257 |
Row rowPriceQuantity = sheet.createRow(rowIndex++);
|
|
|
258 |
int i = 0;
|
|
|
259 |
if (showPartner) {
|
|
|
260 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 6, 6));
|
|
|
261 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 7, 7));
|
|
|
262 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 8, 8));
|
|
|
263 |
}
|
|
|
264 |
sheet.trackAllColumnsForAutoSizing();
|
|
|
265 |
if (showPartner) {
|
|
|
266 |
Cell cellFofoIdHeader = rowHeader.createCell(i++);
|
|
|
267 |
cellFofoIdHeader.setCellValue("Partner Id");
|
|
|
268 |
Cell cellStoreCodeHeader = rowHeader.createCell(i++);
|
|
|
269 |
cellStoreCodeHeader.setCellValue("Store Code");
|
|
|
270 |
Cell cellStoreNameHeader = rowHeader.createCell(i++);
|
|
|
271 |
cellStoreNameHeader.setCellValue("Store Name");
|
|
|
272 |
}
|
|
|
273 |
Cell cellItemIdHeader = rowHeader.createCell(i++);
|
|
|
274 |
cellItemIdHeader.setCellValue("Item Id");
|
|
|
275 |
Cell cellBrandHeader = rowHeader.createCell(i++);
|
|
|
276 |
cellBrandHeader.setCellValue("Brand");
|
|
|
277 |
Cell cellModelNameHeader = rowHeader.createCell(i++);
|
|
|
278 |
cellModelNameHeader.setCellValue("Model Name");
|
|
|
279 |
Cell cellModelNumberHeader = rowHeader.createCell(i++);
|
|
|
280 |
cellModelNumberHeader.setCellValue("Model Number");
|
|
|
281 |
Cell cellColorHeader = rowHeader.createCell(i++);
|
|
|
282 |
cellColorHeader.setCellValue("Color");
|
|
|
283 |
Cell cellTypeHeader = rowHeader.createCell(i++);
|
|
|
284 |
cellTypeHeader.setCellValue("Item Type");
|
|
|
285 |
for (int index = 0; index <= intervals.size(); index++) {
|
|
|
286 |
Cell cellHeader = rowHeader.createCell(i++);
|
|
|
287 |
if (index == 0) {
|
|
|
288 |
cellHeader.setCellValue("Less Than " + intervals.get(index) + " Days");
|
|
|
289 |
} else if (index < intervals.size()) {
|
|
|
290 |
cellHeader.setCellValue(intervals.get(index - 1) + " - " + intervals.get(index) + " Days");
|
|
|
291 |
} else {
|
|
|
292 |
cellHeader.setCellValue("More Than " + intervals.get(index - 1) + " Days");
|
|
|
293 |
}
|
|
|
294 |
sheet.addMergedRegion(new CellRangeAddress(0, 0, i - 1, i));
|
|
|
295 |
rowHeader.createCell(i++);
|
|
|
296 |
Cell cellPrice = rowPriceQuantity.createCell(i - 2);
|
|
|
297 |
cellPrice.setCellValue("Price");
|
|
|
298 |
Cell cellQuantity = rowPriceQuantity.createCell(i - 1);
|
|
|
299 |
cellQuantity.setCellValue("Quantity");
|
|
|
300 |
}
|
|
|
301 |
|
|
|
302 |
Font font = workbook.createFont();
|
|
|
303 |
font.setBold(true);
|
|
|
304 |
CellStyle cellStyle = workbook.createCellStyle();
|
|
|
305 |
cellStyle.setFont(font);
|
|
|
306 |
for (int j = 0; j < nonValueColumns + ((intervals.size() + 1)) * 2; j++) {
|
|
|
307 |
rowHeader.getCell(j).setCellStyle(cellStyle);
|
|
|
308 |
if (rowPriceQuantity.getCell(j) != null) {
|
|
|
309 |
rowPriceQuantity.getCell(j).setCellStyle(cellStyle);
|
|
|
310 |
}
|
|
|
311 |
}
|
|
|
312 |
for (InventoryItemAgingModel inventoryItemAgingModel : inventoryItemAgingModels) {
|
|
|
313 |
i = 0;
|
|
|
314 |
Row rowValues = sheet.createRow(rowIndex++);
|
|
|
315 |
if (showPartner) {
|
|
|
316 |
rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getFofoId());
|
|
|
317 |
rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getStoreCode());
|
|
|
318 |
rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getStoreName());
|
|
|
319 |
}
|
|
|
320 |
rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getItemId());
|
|
|
321 |
rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getBrand());
|
|
|
322 |
rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getModelName());
|
|
|
323 |
rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getModelNumber());
|
|
|
324 |
rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getColor());
|
|
|
325 |
rowValues.createCell(i++).setCellValue(inventoryItemAgingModel.getItemType().toString());
|
|
|
326 |
List<InventoryItemAgingValue> inventoryItemAgingValues = inventoryItemAgingModel.getValues();
|
|
|
327 |
// LOGGER.info("inventoryItemAgingValues {}", inventoryItemAgingValues);
|
|
|
328 |
for (InventoryItemAgingValue inventoryItemAgingValue : inventoryItemAgingValues) {
|
|
|
329 |
if (inventoryItemAgingValue != null) {
|
|
|
330 |
rowValues.createCell(i++).setCellValue(inventoryItemAgingValue.getPrice());
|
|
|
331 |
rowValues.createCell(i++).setCellValue(inventoryItemAgingValue.getQuantity());
|
|
|
332 |
} else {
|
|
|
333 |
rowValues.createCell(i++).setCellValue("-");
|
|
|
334 |
rowValues.createCell(i++).setCellValue("-");
|
|
|
335 |
}
|
|
|
336 |
}
|
|
|
337 |
}
|
|
|
338 |
|
|
|
339 |
for (int index = 0; index < nonValueColumns + ((intervals.size() + 1) * 2); index++) {
|
|
|
340 |
sheet.autoSizeColumn(index);
|
|
|
341 |
}
|
|
|
342 |
|
|
|
343 |
try {
|
|
|
344 |
workbook.write(outputStream);
|
|
|
345 |
workbook.close();
|
|
|
346 |
} catch (IOException ioException) {
|
|
|
347 |
LOGGER.error("Unable to generate excel file", ioException);
|
|
|
348 |
}
|
|
|
349 |
}
|
|
|
350 |
|
|
|
351 |
public static void writeItemCompleteLedgerModels(
|
|
|
352 |
Map<String, List<ItemCompleteLedgerModel>> itemCompleteLedgerModelsMap, OutputStream outputStream) {
|
|
|
353 |
SXSSFWorkbook workbook = new SXSSFWorkbook();
|
|
|
354 |
|
|
|
355 |
// CreationHelper createHelper = workbook.getCreationHelper();
|
|
|
356 |
|
|
|
357 |
for (Map.Entry<String, List<ItemCompleteLedgerModel>> itemCompleteLedgerModelsEntry : itemCompleteLedgerModelsMap
|
|
|
358 |
.entrySet()) {
|
|
|
359 |
SXSSFSheet sheet = workbook.createSheet(itemCompleteLedgerModelsEntry.getKey());
|
|
|
360 |
List<ItemCompleteLedgerModel> itemCompleteLedgerModels = itemCompleteLedgerModelsEntry.getValue();
|
|
|
361 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
|
|
|
362 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
|
|
|
363 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
|
|
|
364 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
|
|
|
365 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
|
|
|
366 |
sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5));
|
|
|
367 |
sheet.trackAllColumnsForAutoSizing();
|
|
|
368 |
|
|
|
369 |
Row rowHeader = sheet.createRow(0);
|
|
|
370 |
Cell cellItemIdHeader = rowHeader.createCell(0);
|
|
|
371 |
cellItemIdHeader.setCellValue("Item Id");
|
|
|
372 |
Cell cellBrandHeader = rowHeader.createCell(1);
|
|
|
373 |
cellBrandHeader.setCellValue("Brand");
|
|
|
374 |
Cell cellModelNameHeader = rowHeader.createCell(2);
|
|
|
375 |
cellModelNameHeader.setCellValue("Model Name");
|
|
|
376 |
Cell cellModelNumberHeader = rowHeader.createCell(3);
|
|
|
377 |
cellModelNumberHeader.setCellValue("Model Number");
|
|
|
378 |
Cell cellColorHeader = rowHeader.createCell(4);
|
|
|
379 |
cellColorHeader.setCellValue("Color");
|
|
|
380 |
Cell cellTypeHeader = rowHeader.createCell(5);
|
|
|
381 |
cellTypeHeader.setCellValue("Item Type");
|
|
|
382 |
Cell cellOpeningBalanceHeader = rowHeader.createCell(6);
|
|
|
383 |
cellOpeningBalanceHeader.setCellValue("Opening Balance");
|
|
|
384 |
Cell cellInwardsHeader = rowHeader.createCell(9);
|
|
|
385 |
cellInwardsHeader.setCellValue("Inwards");
|
|
|
386 |
Cell cellOutwardsHeader = rowHeader.createCell(12);
|
|
|
387 |
cellOutwardsHeader.setCellValue("Outwards");
|
|
|
388 |
Cell cellClosingBalanceHeader = rowHeader.createCell(15);
|
|
|
389 |
cellClosingBalanceHeader.setCellValue("Closing Balance");
|
|
|
390 |
Row rowQuantityRateValue = sheet.createRow(1);
|
|
|
391 |
|
|
|
392 |
for (int index = 6; index < 18; index = index + 3) {
|
|
|
393 |
Cell cellQuantityHeader = rowQuantityRateValue.createCell(index);
|
|
|
394 |
cellQuantityHeader.setCellValue("Quantity");
|
|
|
395 |
Cell cellRateHeader = rowQuantityRateValue.createCell(index + 1);
|
|
|
396 |
cellRateHeader.setCellValue("Rate");
|
|
|
397 |
Cell cellValueHeader = rowQuantityRateValue.createCell(index + 2);
|
|
|
398 |
cellValueHeader.setCellValue("Value");
|
|
|
399 |
sheet.addMergedRegion(new CellRangeAddress(0, 0, index, index + 2));
|
|
|
400 |
}
|
|
|
401 |
Font font = workbook.createFont();
|
|
|
402 |
CellStyle cellStyle = workbook.createCellStyle();
|
|
|
403 |
font.setBold(true);
|
|
|
404 |
cellStyle.setAlignment(HorizontalAlignment.CENTER);
|
|
|
405 |
// font.setFontHeight((short)16);
|
|
|
406 |
cellStyle.setFont(font);
|
|
|
407 |
for (int i = 0; i < 18; i++) {
|
|
|
408 |
if (rowHeader.getCell(i) != null) {
|
|
|
409 |
rowHeader.getCell(i).setCellStyle(cellStyle);
|
|
|
410 |
}
|
|
|
411 |
}
|
|
|
412 |
int openingQuantityTotal = 0;
|
|
|
413 |
float openingValueTotal = 0;
|
|
|
414 |
int inwardsQuantityTotal = 0;
|
|
|
415 |
float inwardsValueTotal = 0;
|
|
|
416 |
int outwardsQuantityTotal = 0;
|
|
|
417 |
float outwardsValueTotal = 0;
|
|
|
418 |
int closingQuantityTotal = 0;
|
|
|
419 |
float closingValueTotal = 0;
|
|
|
420 |
for (int index = 0; index < itemCompleteLedgerModels.size(); index++) {
|
|
|
421 |
ItemCompleteLedgerModel itemCompleteLedgerModel = itemCompleteLedgerModels.get(index);
|
|
|
422 |
Row rowValues = sheet.createRow(index + 2);
|
|
|
423 |
Cell cellItemId = rowValues.createCell(0);
|
|
|
424 |
cellItemId.setCellValue(itemCompleteLedgerModel.getItemValue().getItemId());
|
|
|
425 |
Cell cellBrand = rowValues.createCell(1);
|
|
|
426 |
cellBrand.setCellValue(itemCompleteLedgerModel.getItemValue().getBrand());
|
|
|
427 |
Cell cellModelName = rowValues.createCell(2);
|
|
|
428 |
cellModelName.setCellValue(itemCompleteLedgerModel.getItemValue().getModelName());
|
|
|
429 |
Cell cellModelNumber = rowValues.createCell(3);
|
|
|
430 |
cellModelNumber.setCellValue(itemCompleteLedgerModel.getItemValue().getModelNumber());
|
|
|
431 |
Cell cellColor = rowValues.createCell(4);
|
|
|
432 |
cellColor.setCellValue(itemCompleteLedgerModel.getItemValue().getColor());
|
|
|
433 |
Cell cellType = rowValues.createCell(5);
|
|
|
434 |
cellType.setCellValue(itemCompleteLedgerModel.getItemValue().getItemType().toString());
|
|
|
435 |
Cell cellOpeningQuantity = rowValues.createCell(6);
|
|
|
436 |
Cell cellOpeningRate = rowValues.createCell(7);
|
|
|
437 |
Cell cellOpeningValue = rowValues.createCell(8);
|
|
|
438 |
if (itemCompleteLedgerModel.getOpeningLedger() == null) {
|
|
|
439 |
cellOpeningQuantity.setCellValue("-");
|
|
|
440 |
cellOpeningRate.setCellValue("-");
|
|
|
441 |
cellOpeningValue.setCellValue("-");
|
|
|
442 |
} else {
|
|
|
443 |
cellOpeningQuantity.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getQuantity());
|
|
|
444 |
cellOpeningRate.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getRate());
|
|
|
445 |
cellOpeningValue.setCellValue(itemCompleteLedgerModel.getOpeningLedger().getValue());
|
|
|
446 |
openingQuantityTotal = openingQuantityTotal
|
|
|
447 |
+ itemCompleteLedgerModel.getOpeningLedger().getQuantity();
|
|
|
448 |
openingValueTotal = openingValueTotal + itemCompleteLedgerModel.getOpeningLedger().getValue();
|
|
|
449 |
}
|
|
|
450 |
Cell cellInwardsQuantity = rowValues.createCell(9);
|
|
|
451 |
Cell cellInwardsRate = rowValues.createCell(10);
|
|
|
452 |
Cell cellInwardsValue = rowValues.createCell(11);
|
|
|
453 |
if (itemCompleteLedgerModel.getInwardsLedger() == null) {
|
|
|
454 |
cellInwardsQuantity.setCellValue("-");
|
|
|
455 |
cellInwardsRate.setCellValue("-");
|
|
|
456 |
cellInwardsValue.setCellValue("-");
|
|
|
457 |
} else {
|
|
|
458 |
cellInwardsQuantity.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getQuantity());
|
|
|
459 |
cellInwardsRate.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getRate());
|
|
|
460 |
cellInwardsValue.setCellValue(itemCompleteLedgerModel.getInwardsLedger().getValue());
|
|
|
461 |
inwardsQuantityTotal = inwardsQuantityTotal
|
|
|
462 |
+ itemCompleteLedgerModel.getInwardsLedger().getQuantity();
|
|
|
463 |
inwardsValueTotal = inwardsValueTotal + itemCompleteLedgerModel.getInwardsLedger().getValue();
|
|
|
464 |
}
|
|
|
465 |
Cell cellOutwardsQuantity = rowValues.createCell(12);
|
|
|
466 |
Cell cellOutwardsRate = rowValues.createCell(13);
|
|
|
467 |
Cell cellOutwardsValue = rowValues.createCell(14);
|
|
|
468 |
if (itemCompleteLedgerModel.getOutwardsLedger() == null) {
|
|
|
469 |
cellOutwardsQuantity.setCellValue("-");
|
|
|
470 |
cellOutwardsRate.setCellValue("-");
|
|
|
471 |
cellOutwardsValue.setCellValue("-");
|
|
|
472 |
} else {
|
|
|
473 |
cellOutwardsQuantity.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getQuantity());
|
|
|
474 |
cellOutwardsRate.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getRate());
|
|
|
475 |
cellOutwardsValue.setCellValue(itemCompleteLedgerModel.getOutwardsLedger().getValue());
|
|
|
476 |
outwardsQuantityTotal = outwardsQuantityTotal
|
|
|
477 |
+ itemCompleteLedgerModel.getOutwardsLedger().getQuantity();
|
|
|
478 |
outwardsValueTotal = outwardsValueTotal + itemCompleteLedgerModel.getOutwardsLedger().getValue();
|
|
|
479 |
}
|
|
|
480 |
Cell cellClosingQuantity = rowValues.createCell(15);
|
|
|
481 |
Cell cellClosingRate = rowValues.createCell(16);
|
|
|
482 |
Cell cellClosingValue = rowValues.createCell(17);
|
|
|
483 |
if (itemCompleteLedgerModel.getClosingLedger() == null) {
|
|
|
484 |
cellClosingQuantity.setCellValue("-");
|
|
|
485 |
cellClosingRate.setCellValue("-");
|
|
|
486 |
cellClosingValue.setCellValue("-");
|
|
|
487 |
} else {
|
|
|
488 |
cellClosingQuantity.setCellValue(itemCompleteLedgerModel.getClosingLedger().getQuantity());
|
|
|
489 |
cellClosingRate.setCellValue(itemCompleteLedgerModel.getClosingLedger().getRate());
|
|
|
490 |
cellClosingValue.setCellValue(itemCompleteLedgerModel.getClosingLedger().getValue());
|
|
|
491 |
closingQuantityTotal = closingQuantityTotal
|
|
|
492 |
+ itemCompleteLedgerModel.getClosingLedger().getQuantity();
|
|
|
493 |
closingValueTotal = closingValueTotal + itemCompleteLedgerModel.getClosingLedger().getValue();
|
|
|
494 |
}
|
|
|
495 |
}
|
|
|
496 |
|
|
|
497 |
Row rowTotal = sheet.createRow(itemCompleteLedgerModels.size() + 2);
|
|
|
498 |
|
|
|
499 |
if (openingQuantityTotal > 0) {
|
|
|
500 |
Cell cellOpeningQuantityTotal = rowTotal.createCell(6);
|
|
|
501 |
cellOpeningQuantityTotal.setCellValue(openingQuantityTotal);
|
|
|
502 |
Cell cellOpeningValueTotal = rowTotal.createCell(8);
|
|
|
503 |
cellOpeningValueTotal.setCellValue(openingValueTotal);
|
|
|
504 |
}
|
|
|
505 |
|
|
|
506 |
if (inwardsQuantityTotal > 0) {
|
|
|
507 |
Cell cellInwardsQuantityTotal = rowTotal.createCell(9);
|
|
|
508 |
cellInwardsQuantityTotal.setCellValue(inwardsQuantityTotal);
|
|
|
509 |
Cell cellInwardsValueTotal = rowTotal.createCell(11);
|
|
|
510 |
cellInwardsValueTotal.setCellValue(inwardsValueTotal);
|
|
|
511 |
}
|
|
|
512 |
|
|
|
513 |
if (outwardsQuantityTotal > 0) {
|
|
|
514 |
Cell cellOutwardsQuantityTotal = rowTotal.createCell(12);
|
|
|
515 |
cellOutwardsQuantityTotal.setCellValue(outwardsQuantityTotal);
|
|
|
516 |
Cell cellOutwardsValueTotal = rowTotal.createCell(14);
|
|
|
517 |
cellOutwardsValueTotal.setCellValue(outwardsValueTotal);
|
|
|
518 |
}
|
|
|
519 |
|
|
|
520 |
if (closingQuantityTotal > 0) {
|
|
|
521 |
Cell cellClosingQuantityTotal = rowTotal.createCell(15);
|
|
|
522 |
cellClosingQuantityTotal.setCellValue(closingQuantityTotal);
|
|
|
523 |
Cell cellClosingValueTotal = rowTotal.createCell(17);
|
|
|
524 |
cellClosingValueTotal.setCellValue(closingValueTotal);
|
|
|
525 |
}
|
|
|
526 |
|
|
|
527 |
for (int index = 0; index < 18; index++) {
|
|
|
528 |
sheet.autoSizeColumn(index);
|
|
|
529 |
}
|
|
|
530 |
}
|
|
|
531 |
try {
|
|
|
532 |
workbook.write(outputStream);
|
|
|
533 |
workbook.close();
|
|
|
534 |
} catch (IOException ioException) {
|
|
|
535 |
LOGGER.error("Unable to generate excel file", ioException);
|
|
|
536 |
}
|
|
|
537 |
}
|
|
|
538 |
|
|
|
539 |
public static void writeSchemeModels(List<SchemeModel> schemeModels, OutputStream outputStream) {
|
|
|
540 |
SXSSFWorkbook workbook = new SXSSFWorkbook();
|
|
|
541 |
|
|
|
542 |
// CreationHelper createHelper = workbook.getCreationHelper();
|
|
|
543 |
|
|
|
544 |
SXSSFSheet sheet = workbook.createSheet("Schemes");
|
|
|
545 |
sheet.trackAllColumnsForAutoSizing();
|
|
|
546 |
|
|
|
547 |
Row rowHeader = sheet.createRow(0);
|
|
|
548 |
Cell cellSchemeIdHeader = rowHeader.createCell(0);
|
|
|
549 |
cellSchemeIdHeader.setCellValue("Scheme Id");
|
|
|
550 |
Cell cellNameHeader = rowHeader.createCell(1);
|
|
|
551 |
cellNameHeader.setCellValue("Name");
|
|
|
552 |
Cell cellDescriptionHeader = rowHeader.createCell(2);
|
|
|
553 |
cellDescriptionHeader.setCellValue("Description");
|
|
|
554 |
Cell cellSchemeTypeHeader = rowHeader.createCell(3);
|
|
|
555 |
cellSchemeTypeHeader.setCellValue("Scheme Type");
|
|
|
556 |
Cell cellAmountTypeHeader = rowHeader.createCell(4);
|
|
|
557 |
cellAmountTypeHeader.setCellValue("Amount Type");
|
|
|
558 |
Cell cellAmountHeader = rowHeader.createCell(5);
|
|
|
559 |
cellAmountHeader.setCellValue("Amount");
|
|
|
560 |
Cell cellStartDateTimeHeader = rowHeader.createCell(6);
|
|
|
561 |
cellStartDateTimeHeader.setCellValue("Start Date Time");
|
|
|
562 |
Cell cellEndDateTimeHeader = rowHeader.createCell(7);
|
|
|
563 |
cellEndDateTimeHeader.setCellValue("End Date Time");
|
|
|
564 |
Cell cellCreatedAtHeader = rowHeader.createCell(8);
|
|
|
565 |
cellCreatedAtHeader.setCellValue("Created At");
|
|
|
566 |
Cell cellActiveHeader = rowHeader.createCell(9);
|
|
|
567 |
cellActiveHeader.setCellValue("Active");
|
|
|
568 |
Cell cellExpireHeader = rowHeader.createCell(10);
|
|
|
569 |
cellExpireHeader.setCellValue("Expire");
|
|
|
570 |
Cell cellCreatedByHeader = rowHeader.createCell(11);
|
|
|
571 |
cellCreatedByHeader.setCellValue("Created By");
|
|
|
572 |
Cell cellItemIdsHeader = rowHeader.createCell(12);
|
|
|
573 |
cellItemIdsHeader.setCellValue("Item Ids");
|
|
|
574 |
Cell cellRetailerIdsHeader = rowHeader.createCell(13);
|
|
|
575 |
cellRetailerIdsHeader.setCellValue("Retailer Ids");
|
|
|
576 |
// Row rowQuantityRateValue = sheet.createRow(1);
|
|
|
577 |
|
|
|
578 |
/*
|
|
|
579 |
* for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
|
|
|
580 |
* rowQuantityRateValue.createCell(index);
|
|
|
581 |
* cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
|
|
|
582 |
* rowQuantityRateValue.createCell(index + 1);
|
|
|
583 |
* cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
|
|
|
584 |
* rowQuantityRateValue.createCell(index + 2);
|
|
|
585 |
* cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
|
|
|
586 |
* CellRangeAddress(0, 0, index, index + 2)); }
|
|
|
587 |
*/
|
|
|
588 |
Font font = workbook.createFont();
|
|
|
589 |
CellStyle cellStyle = workbook.createCellStyle();
|
|
|
590 |
font.setBold(true);
|
|
|
591 |
cellStyle.setAlignment(HorizontalAlignment.CENTER);
|
|
|
592 |
// font.setFontHeight((short)16);
|
|
|
593 |
cellStyle.setFont(font);
|
|
|
594 |
for (int i = 0; i < 14; i++) {
|
|
|
595 |
if (rowHeader.getCell(i) != null) {
|
|
|
596 |
rowHeader.getCell(i).setCellStyle(cellStyle);
|
|
|
597 |
}
|
|
|
598 |
}
|
|
|
599 |
|
|
|
600 |
for (int index = 0; index < schemeModels.size(); index++) {
|
|
|
601 |
SchemeModel schemeModel = schemeModels.get(index);
|
|
|
602 |
// ItemCompleteLedgerModel itemCompleteLedgerModel =
|
|
|
603 |
// itemCompleteLedgerModels.get(index);
|
|
|
604 |
Row rowValues = sheet.createRow(index + 1);
|
|
|
605 |
Cell cellSchemeId = rowValues.createCell(0);
|
|
|
606 |
cellSchemeId.setCellValue(schemeModel.getSchemeId());
|
|
|
607 |
Cell cellName = rowValues.createCell(1);
|
|
|
608 |
cellName.setCellValue(schemeModel.getName());
|
|
|
609 |
Cell cellDescription = rowValues.createCell(2);
|
|
|
610 |
cellDescription.setCellValue(schemeModel.getDescription());
|
|
|
611 |
Cell cellSchemeType = rowValues.createCell(3);
|
|
|
612 |
cellSchemeType.setCellValue(schemeModel.getSchemeType());
|
|
|
613 |
Cell cellAmountType = rowValues.createCell(4);
|
|
|
614 |
cellAmountType.setCellValue(schemeModel.getAmountType());
|
|
|
615 |
Cell cellAmount = rowValues.createCell(5);
|
|
|
616 |
cellAmount.setCellValue(schemeModel.getAmount());
|
|
|
617 |
Cell cellStartDateTime = rowValues.createCell(6);
|
|
|
618 |
cellStartDateTime.setCellValue(schemeModel.getStartDateTime());
|
|
|
619 |
Cell cellEndDateTime = rowValues.createCell(7);
|
|
|
620 |
cellEndDateTime.setCellValue(schemeModel.getEndDateTime());
|
|
|
621 |
Cell cellCreatedAt = rowValues.createCell(8);
|
|
|
622 |
cellCreatedAt.setCellValue(schemeModel.getCreateTimestamp());
|
|
|
623 |
Cell cellActive = rowValues.createCell(9);
|
|
|
624 |
if (schemeModel.getActiveTimestamp() != null) {
|
|
|
625 |
cellActive.setCellValue(schemeModel.getActiveTimestamp());
|
|
|
626 |
} else {
|
|
|
627 |
cellActive.setCellValue("False");
|
|
|
628 |
}
|
|
|
629 |
Cell cellExpire = rowValues.createCell(10);
|
|
|
630 |
if (schemeModel.getExpireTimestamp() != null) {
|
|
|
631 |
cellExpire.setCellValue(schemeModel.getExpireTimestamp());
|
|
|
632 |
} else {
|
|
|
633 |
cellExpire.setCellValue("False");
|
|
|
634 |
}
|
|
|
635 |
Cell cellCreatedBy = rowValues.createCell(11);
|
|
|
636 |
cellCreatedBy.setCellValue(schemeModel.getCreatedBy());
|
|
|
637 |
Cell cellItemIds = rowValues.createCell(12);
|
|
|
638 |
cellItemIds.setCellValue(schemeModel.getCatalogStringMap().toString());
|
|
|
639 |
Cell cellRetailerIds = rowValues.createCell(13);
|
|
|
640 |
cellRetailerIds.setCellValue(schemeModel.getRetailerIdsString());
|
|
|
641 |
int maxHeight = Math.max(schemeModel.getCatalogStringMap().size(), schemeModel.getRetailerIds().size());
|
|
|
642 |
if (maxHeight > 1) {
|
|
|
643 |
rowValues.setHeight((short) (maxHeight * 240));
|
|
|
644 |
}
|
|
|
645 |
}
|
|
|
646 |
|
|
|
647 |
for (int index = 0; index < 14; index++) {
|
|
|
648 |
sheet.autoSizeColumn(index);
|
|
|
649 |
}
|
|
|
650 |
|
|
|
651 |
try {
|
|
|
652 |
workbook.write(outputStream);
|
|
|
653 |
workbook.close();
|
|
|
654 |
} catch (IOException ioException) {
|
|
|
655 |
LOGGER.error("Unable to generate excel file", ioException);
|
|
|
656 |
}
|
|
|
657 |
}
|
|
|
658 |
|
|
|
659 |
public static void writePriceDrop(Map<String, String> priceDropIMEIfofoId, int itemId, OutputStream outputStream) {
|
|
|
660 |
SXSSFWorkbook workbook = new SXSSFWorkbook();
|
|
|
661 |
|
|
|
662 |
// CreationHelper createHelper = workbook.getCreationHelper();
|
|
|
663 |
|
|
|
664 |
SXSSFSheet sheet = workbook.createSheet("Schemes");
|
|
|
665 |
sheet.trackAllColumnsForAutoSizing();
|
|
|
666 |
|
|
|
667 |
Row rowHeader = sheet.createRow(0);
|
|
|
668 |
Cell cellItemHeader = rowHeader.createCell(0);
|
|
|
669 |
cellItemHeader.setCellValue("ITEMID");
|
|
|
670 |
Cell cellIMEIHeader = rowHeader.createCell(1);
|
|
|
671 |
cellIMEIHeader.setCellValue("IMEI");
|
|
|
672 |
Cell cellFOFOIDHeader = rowHeader.createCell(2);
|
|
|
673 |
cellFOFOIDHeader.setCellValue("RETAILERNAME");
|
|
|
674 |
// Row rowQuantityRateValue = sheet.createRow(1);
|
|
|
675 |
|
|
|
676 |
/*
|
|
|
677 |
* for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
|
|
|
678 |
* rowQuantityRateValue.createCell(index);
|
|
|
679 |
* cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
|
|
|
680 |
* rowQuantityRateValue.createCell(index + 1);
|
|
|
681 |
* cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
|
|
|
682 |
* rowQuantityRateValue.createCell(index + 2);
|
|
|
683 |
* cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
|
|
|
684 |
* CellRangeAddress(0, 0, index, index + 2)); }
|
|
|
685 |
*/
|
|
|
686 |
Font font = workbook.createFont();
|
|
|
687 |
CellStyle cellStyle = workbook.createCellStyle();
|
|
|
688 |
font.setBold(true);
|
|
|
689 |
cellStyle.setAlignment(HorizontalAlignment.CENTER);
|
|
|
690 |
// font.setFontHeight((short)16);
|
|
|
691 |
cellStyle.setFont(font);
|
|
|
692 |
for (int i = 0; i < 14; i++) {
|
|
|
693 |
if (rowHeader.getCell(i) != null) {
|
|
|
694 |
rowHeader.getCell(i).setCellStyle(cellStyle);
|
|
|
695 |
}
|
|
|
696 |
}
|
|
|
697 |
LinkedHashMap<String, String> priceAmount = new LinkedHashMap<>(priceDropIMEIfofoId);
|
|
|
698 |
Set<String> keyset = priceAmount.keySet();
|
|
|
699 |
int rownum = 1;
|
|
|
700 |
for (String key : keyset) {
|
|
|
701 |
Row row = sheet.createRow(rownum++);
|
|
|
702 |
String objArr = priceAmount.get(key);
|
|
|
703 |
int cellnum = 0;
|
|
|
704 |
Cell cell = row.createCell(cellnum++);
|
|
|
705 |
cell.setCellValue(itemId);
|
|
|
706 |
Cell cell1 = row.createCell(cellnum++);
|
|
|
707 |
cell1.setCellValue(key);
|
|
|
708 |
Cell cell2 = row.createCell(cellnum++);
|
|
|
709 |
cell2.setCellValue(priceAmount.get(key));
|
|
|
710 |
}
|
|
|
711 |
|
|
|
712 |
for (int index = 0; index < 14; index++) {
|
|
|
713 |
sheet.autoSizeColumn(index);
|
|
|
714 |
}
|
|
|
715 |
|
|
|
716 |
try {
|
|
|
717 |
workbook.write(outputStream);
|
|
|
718 |
workbook.close();
|
|
|
719 |
} catch (IOException ioException) {
|
|
|
720 |
LOGGER.error("Unable to generate excel file", ioException);
|
|
|
721 |
}
|
|
|
722 |
}
|
|
|
723 |
|
|
|
724 |
public static void writePriceDropForAllIMEI(Map<String, String> priceDropAmount, OutputStream outputStream) {
|
|
|
725 |
SXSSFWorkbook workbook = new SXSSFWorkbook();
|
|
|
726 |
|
|
|
727 |
// CreationHelper createHelper = workbook.getCreationHelper();
|
|
|
728 |
|
|
|
729 |
SXSSFSheet sheet = workbook.createSheet("Schemes");
|
|
|
730 |
sheet.trackAllColumnsForAutoSizing();
|
|
|
731 |
|
|
|
732 |
Row rowHeader = sheet.createRow(0);
|
|
|
733 |
Cell cellItemHeader = rowHeader.createCell(0);
|
|
|
734 |
cellItemHeader.setCellValue("Itemdescription");
|
|
|
735 |
Cell cellIMEIHeader = rowHeader.createCell(1);
|
|
|
736 |
cellIMEIHeader.setCellValue("IMEI");
|
|
|
737 |
// Row rowQuantityRateValue = sheet.createRow(1);
|
|
|
738 |
|
|
|
739 |
/*
|
|
|
740 |
* for(int index = 6; index < 18; index = index + 3){ Cell cellQuantityHeader =
|
|
|
741 |
* rowQuantityRateValue.createCell(index);
|
|
|
742 |
* cellQuantityHeader.setCellValue("Quantity"); Cell cellRateHeader =
|
|
|
743 |
* rowQuantityRateValue.createCell(index + 1);
|
|
|
744 |
* cellRateHeader.setCellValue("Rate"); Cell cellValueHeader =
|
|
|
745 |
* rowQuantityRateValue.createCell(index + 2);
|
|
|
746 |
* cellValueHeader.setCellValue("Value"); sheet.addMergedRegion(new
|
|
|
747 |
* CellRangeAddress(0, 0, index, index + 2)); }
|
|
|
748 |
*/
|
|
|
749 |
Font font = workbook.createFont();
|
|
|
750 |
CellStyle cellStyle = workbook.createCellStyle();
|
|
|
751 |
font.setBold(true);
|
|
|
752 |
cellStyle.setAlignment(HorizontalAlignment.CENTER);
|
|
|
753 |
// font.setFontHeight((short)16);
|
|
|
754 |
cellStyle.setFont(font);
|
|
|
755 |
for (int i = 0; i < 14; i++) {
|
|
|
756 |
if (rowHeader.getCell(i) != null) {
|
|
|
757 |
rowHeader.getCell(i).setCellStyle(cellStyle);
|
|
|
758 |
}
|
|
|
759 |
}
|
|
|
760 |
Set<String> keyset = priceDropAmount.keySet();
|
|
|
761 |
int rownum = 1;
|
|
|
762 |
for (String key : keyset) {
|
|
|
763 |
Row row = sheet.createRow(rownum++);
|
|
|
764 |
String objArr = priceDropAmount.get(key);
|
|
|
765 |
int cellnum = 0;
|
|
|
766 |
Cell cell = row.createCell(cellnum++);
|
|
|
767 |
cell.setCellValue(priceDropAmount.get(key));
|
|
|
768 |
Cell cell1 = row.createCell(cellnum++);
|
|
|
769 |
cell1.setCellValue(key);
|
|
|
770 |
}
|
|
|
771 |
|
|
|
772 |
for (int index = 0; index < 14; index++) {
|
|
|
773 |
sheet.autoSizeColumn(index);
|
|
|
774 |
}
|
|
|
775 |
|
|
|
776 |
try {
|
|
|
777 |
workbook.write(outputStream);
|
|
|
778 |
workbook.close();
|
|
|
779 |
} catch (IOException ioException) {
|
|
|
780 |
LOGGER.error("Unable to generate excel file", ioException);
|
|
|
781 |
}
|
|
|
782 |
}
|
|
|
783 |
|
|
|
784 |
public static void writeDailySaleReportVsTargetForPartner(Map<Integer, String> targetIdAndTargetNameMap,
|
|
|
785 |
Map<Integer, Float> targetIdAndtargetValuesMap, Map<Integer, Double> targetIdAndsaleValuesMap,
|
|
|
786 |
Map<Integer, CustomRetailer> targetIdAndCustomRetailerMap, Map<Integer, String> targetIdAndSlabNamesMap,
|
|
|
787 |
Map<Integer, Double> targetIdAndsaleValuesPercentageMap,
|
|
|
788 |
Map<Integer, Float> targetIdAndtargetdailyAverageSaleMap,
|
|
|
789 |
Map<Integer, Double> targetIdAndRemainingTargetMap, Map<Integer, Double> targetIdAndtodayAchievementsMap,
|
|
|
790 |
Map<Integer, String> targetIdAndSalesHeadMap, OutputStream outputStream) {
|
|
|
791 |
SXSSFWorkbook workbook = new SXSSFWorkbook();
|
|
|
792 |
|
|
|
793 |
// CreationHelper createHelper = workbook.getCreationHelper();
|
|
|
794 |
|
|
|
795 |
SXSSFSheet sheet = workbook.createSheet("DailySaleReports");
|
|
|
796 |
sheet.trackAllColumnsForAutoSizing();
|
|
|
797 |
|
|
|
798 |
Row rowHeader = sheet.createRow(0);
|
|
|
799 |
Cell cellStoreName = rowHeader.createCell(0);
|
|
|
800 |
cellStoreName.setCellValue("Store Name");
|
|
|
801 |
Cell cellBusiness = rowHeader.createCell(1);
|
|
|
802 |
cellBusiness.setCellValue("Business Manager");
|
|
|
803 |
Cell cellAssistant = rowHeader.createCell(2);
|
|
|
804 |
cellAssistant.setCellValue("Assistant Manager");
|
|
|
805 |
Cell cellSchemeName = rowHeader.createCell(3);
|
|
|
806 |
cellSchemeName.setCellValue("Scheme Name");
|
|
|
807 |
Cell cellTargetValue = rowHeader.createCell(4);
|
|
|
808 |
cellTargetValue.setCellValue("Scheme Target");
|
|
|
809 |
Cell cellMonthlySaleValue = rowHeader.createCell(5);
|
|
|
810 |
cellMonthlySaleValue.setCellValue("Total Achievement");
|
|
|
811 |
Cell cellMonthlySaleValuePercentage = rowHeader.createCell(6);
|
|
|
812 |
cellMonthlySaleValuePercentage.setCellValue("Today Achievement Percentage");
|
|
|
813 |
Cell cellDailyTargetValue = rowHeader.createCell(7);
|
|
|
814 |
cellDailyTargetValue.setCellValue("Daily Target");
|
|
|
815 |
Cell cellTodayAchievement = rowHeader.createCell(8);
|
|
|
816 |
cellTodayAchievement.setCellValue("Today Achievement");
|
|
|
817 |
Cell cellRemainingTarget = rowHeader.createCell(9);
|
|
|
818 |
cellRemainingTarget.setCellValue("Remaining Target");
|
|
|
819 |
Cell cellEligibility = rowHeader.createCell(10);
|
|
|
820 |
cellEligibility.setCellValue("Eligibility");
|
|
|
821 |
Font font = workbook.createFont();
|
|
|
822 |
CellStyle cellStyle = workbook.createCellStyle();
|
|
|
823 |
font.setBold(true);
|
|
|
824 |
cellStyle.setAlignment(HorizontalAlignment.CENTER);
|
|
|
825 |
// font.setFontHeight((short)16);
|
|
|
826 |
cellStyle.setFont(font);
|
|
|
827 |
for (int i = 0; i < 14; i++) {
|
|
|
828 |
if (rowHeader.getCell(i) != null) {
|
|
|
829 |
rowHeader.getCell(i).setCellStyle(cellStyle);
|
|
|
830 |
}
|
|
|
831 |
}
|
|
|
832 |
int rownum = 1;
|
|
|
833 |
for (Integer targetId : targetIdAndTargetNameMap.keySet()) {
|
|
|
834 |
Row row = sheet.createRow(rownum++);
|
|
|
835 |
int cellnum = 0;
|
|
|
836 |
Cell cellPartner = row.createCell(cellnum++);
|
|
|
837 |
if (targetIdAndCustomRetailerMap.get(targetId) != null) {
|
|
|
838 |
cellPartner.setCellValue(targetIdAndCustomRetailerMap.get(targetId).getBusinessName());
|
|
|
839 |
} else {
|
|
|
840 |
cellPartner.setCellValue("-");
|
|
|
841 |
}
|
|
|
842 |
|
|
|
843 |
Cell cellBusinessManager = row.createCell(cellnum++);
|
|
|
844 |
if (targetIdAndSalesHeadMap.get(targetId) != null) {
|
|
|
845 |
if (targetIdAndSalesHeadMap.get(targetId).equals("Kamal")) {
|
|
|
846 |
cellBusinessManager.setCellValue("Mohinder");
|
|
|
847 |
} else {
|
|
|
848 |
cellBusinessManager.setCellValue(targetIdAndSalesHeadMap.get(targetId));
|
|
|
849 |
}
|
|
|
850 |
} else {
|
|
|
851 |
cellBusinessManager.setCellValue("-");
|
|
|
852 |
}
|
|
|
853 |
Cell cellAssistantManager = row.createCell(cellnum++);
|
|
|
854 |
if (targetIdAndSalesHeadMap.get(targetId) != null) {
|
|
|
855 |
if (targetIdAndSalesHeadMap.get(targetId).equals("Kamal")) {
|
|
|
856 |
cellAssistantManager.setCellValue(targetIdAndSalesHeadMap.get(targetId));
|
|
|
857 |
} else {
|
|
|
858 |
cellAssistantManager.setCellValue("");
|
|
|
859 |
}
|
|
|
860 |
} else {
|
|
|
861 |
cellAssistantManager.setCellValue("");
|
|
|
862 |
}
|
|
|
863 |
Cell cellTargetName = row.createCell(cellnum++);
|
|
|
864 |
if (targetIdAndTargetNameMap.get(targetId) == null || targetIdAndTargetNameMap.get(targetId) == "") {
|
|
|
865 |
cellTargetName.setCellValue("");
|
|
|
866 |
|
|
|
867 |
} else {
|
|
|
868 |
cellTargetName.setCellValue(targetIdAndTargetNameMap.get(targetId));
|
|
|
869 |
}
|
|
|
870 |
Cell cellMTDTargetValue = row.createCell(cellnum++);
|
|
|
871 |
if (targetIdAndtargetValuesMap.get(targetId) != null) {
|
|
|
872 |
cellMTDTargetValue.setCellValue(targetIdAndtargetValuesMap.get(targetId));
|
|
|
873 |
} else {
|
|
|
874 |
cellMTDTargetValue.setCellValue("-");
|
|
|
875 |
}
|
|
|
876 |
Cell cellMTDAchievement = row.createCell(cellnum++);
|
|
|
877 |
if (targetIdAndsaleValuesMap.get(targetId) != null) {
|
|
|
878 |
cellMTDAchievement.setCellValue(targetIdAndsaleValuesMap.get(targetId));
|
|
|
879 |
} else {
|
|
|
880 |
cellMTDAchievement.setCellValue(0);
|
|
|
881 |
}
|
|
|
882 |
Cell cellMTDAchievementPercentage = row.createCell(cellnum++);
|
|
|
883 |
if (targetIdAndsaleValuesPercentageMap.get(targetId) != null) {
|
|
|
884 |
cellMTDAchievementPercentage.setCellValue(targetIdAndsaleValuesPercentageMap.get(targetId) + "%");
|
|
|
885 |
} else {
|
|
|
886 |
cellMTDAchievementPercentage.setCellValue(0 + "%");
|
|
|
887 |
}
|
|
|
888 |
|
|
|
889 |
Cell cellDailyTargetValue1 = row.createCell(cellnum++);
|
|
|
890 |
if (targetIdAndtargetdailyAverageSaleMap.get(targetId) != null) {
|
|
|
891 |
String formatting = FormattingUtils
|
|
|
892 |
.formatDecimal(targetIdAndtargetdailyAverageSaleMap.get(targetId));
|
|
|
893 |
cellDailyTargetValue1.setCellValue(formatting);
|
|
|
894 |
} else {
|
|
|
895 |
cellDailyTargetValue1.setCellValue(0);
|
|
|
896 |
}
|
|
|
897 |
Cell cellTodayAchieveMentSaleValue = row.createCell(cellnum++);
|
|
|
898 |
if (targetIdAndtodayAchievementsMap.get(targetId) != null) {
|
|
|
899 |
cellTodayAchieveMentSaleValue.setCellValue(targetIdAndtodayAchievementsMap.get(targetId));
|
|
|
900 |
} else {
|
|
|
901 |
cellTodayAchieveMentSaleValue.setCellValue(0);
|
|
|
902 |
}
|
|
|
903 |
|
|
|
904 |
Cell cellRemaining = row.createCell(cellnum++);
|
|
|
905 |
if (targetIdAndRemainingTargetMap.get(targetId) != null) {
|
|
|
906 |
cellRemaining.setCellValue(targetIdAndRemainingTargetMap.get(targetId));
|
|
|
907 |
} else {
|
|
|
908 |
cellRemaining.setCellValue(0);
|
|
|
909 |
}
|
|
|
910 |
|
|
|
911 |
Cell cellEligible = row.createCell(cellnum++);
|
|
|
912 |
if (targetIdAndSlabNamesMap.get(targetId) != null) {
|
|
|
913 |
cellEligible.setCellValue(targetIdAndSlabNamesMap.get(targetId));
|
|
|
914 |
} else {
|
|
|
915 |
cellEligible.setCellValue("-");
|
|
|
916 |
}
|
|
|
917 |
}
|
|
|
918 |
|
|
|
919 |
for (int index = 0; index < targetIdAndsaleValuesMap.size(); index++) {
|
|
|
920 |
sheet.autoSizeColumn(index);
|
|
|
921 |
}
|
|
|
922 |
|
|
|
923 |
try {
|
|
|
924 |
workbook.write(outputStream);
|
|
|
925 |
workbook.close();
|
|
|
926 |
} catch (IOException ioException) {
|
|
|
927 |
LOGGER.error("Unable to generate excel file", ioException);
|
|
|
928 |
}
|
|
|
929 |
}
|
|
|
930 |
|
|
|
931 |
public static List<WalletHistoryModel> parseWalletBulkCredit(InputStream inputStream) throws Exception {
|
|
|
932 |
List<WalletHistoryModel> walletHistoryModels = new ArrayList<>();
|
|
|
933 |
try (XSSFWorkbook myWorkBook = new XSSFWorkbook(inputStream)) {
|
|
|
934 |
|
|
|
935 |
myWorkBook.setMissingCellPolicy(MissingCellPolicy.RETURN_BLANK_AS_NULL);
|
|
|
936 |
// Return first sheet from the XLSX workbook
|
|
|
937 |
XSSFSheet mySheet = myWorkBook.getSheetAt(0);
|
|
|
938 |
LOGGER.info("rowCellNum {}", mySheet.getLastRowNum());
|
|
|
939 |
|
|
|
940 |
for (int rowNumber = 1; rowNumber <= mySheet.getLastRowNum(); rowNumber++) {
|
|
|
941 |
XSSFRow row = mySheet.getRow(rowNumber);
|
|
|
942 |
LOGGER.info("row {}", row);
|
|
|
943 |
WalletHistoryModel walletHistoryModel = new WalletHistoryModel();
|
|
|
944 |
if (row.getCell(1) != null && row.getCell(1).getCellTypeEnum() == CellType.NUMERIC) {
|
|
|
945 |
walletHistoryModel.setFofoId((int) row.getCell(1).getNumericCellValue());
|
|
|
946 |
} else {
|
|
|
947 |
throw new ProfitMandiBusinessException("Invalid Fofo Id", "row number " + rowNumber,
|
|
|
948 |
"row number " + rowNumber);
|
|
|
949 |
}
|
|
|
950 |
if (row.getCell(2) != null && row.getCell(2).getCellTypeEnum() == CellType.STRING) {
|
|
|
951 |
WalletReferenceType referenceType = WalletReferenceType
|
|
|
952 |
.valueOf(row.getCell(2).getStringCellValue());
|
|
|
953 |
if (!BULK_WALLET_REFERENCES.contains(referenceType)) {
|
|
|
954 |
throw new ProfitMandiBusinessException("Invalid Reference type", "row number " + rowNumber,
|
|
|
955 |
"row number " + rowNumber);
|
|
|
956 |
}
|
|
|
957 |
walletHistoryModel
|
|
|
958 |
.setWalletReferenceType(WalletReferenceType.valueOf(row.getCell(2).getStringCellValue()));
|
|
|
959 |
} else {
|
|
|
960 |
throw new ProfitMandiBusinessException("Invalid ReferenceType", "row number " + rowNumber,
|
|
|
961 |
"row number " + rowNumber);
|
|
|
962 |
}
|
|
|
963 |
if (row.getCell(3) != null && row.getCell(3).getCellTypeEnum() == CellType.NUMERIC) {
|
|
|
964 |
Date businessDate = row.getCell(3).getDateCellValue();
|
|
|
965 |
LocalDateTime businessDateTime = LocalDateTime.ofInstant(businessDate.toInstant(), ZoneId.systemDefault());
|
|
|
966 |
walletHistoryModel.setBusinessDate(businessDateTime);
|
|
|
967 |
;
|
|
|
968 |
} else {
|
|
|
969 |
throw new ProfitMandiBusinessException("Invalid Business Date", "row number " + rowNumber,
|
|
|
970 |
"row number " + rowNumber);
|
|
|
971 |
}
|
|
|
972 |
if (row.getCell(4) != null && row.getCell(4).getCellTypeEnum() == CellType.NUMERIC) {
|
|
|
973 |
double amount = row.getCell(4).getNumericCellValue();
|
|
|
974 |
if (amount < 0) {
|
|
|
975 |
throw new ProfitMandiBusinessException("Invalid Amount, only positive values",
|
|
|
976 |
"row number " + rowNumber, "row number " + rowNumber);
|
|
|
977 |
}
|
|
|
978 |
walletHistoryModel.setAmount(amount);
|
|
|
979 |
} else {
|
|
|
980 |
throw new ProfitMandiBusinessException("Invalid Amount", "row number " + rowNumber,
|
|
|
981 |
"row number " + rowNumber);
|
|
|
982 |
}
|
|
|
983 |
if (row.getCell(5) != null && row.getCell(5).getCellTypeEnum() == CellType.STRING) {
|
|
|
984 |
String transactionType = row.getCell(5).getStringCellValue().toLowerCase();
|
|
|
985 |
if (transactionType.equals("dr")) {
|
|
|
986 |
walletHistoryModel.setAmount(-walletHistoryModel.getAmount());
|
|
|
987 |
} else if (!transactionType.equals("cr")) {
|
|
|
988 |
throw new ProfitMandiBusinessException("Invalid Transaction Type", "row number " + rowNumber,
|
|
|
989 |
"row number " + rowNumber);
|
|
|
990 |
}
|
|
|
991 |
} else {
|
|
|
992 |
throw new ProfitMandiBusinessException("Invalid Transaction Type", "row number " + rowNumber,
|
|
|
993 |
"row number " + rowNumber);
|
|
|
994 |
}
|
|
|
995 |
if (row.getCell(6) != null && row.getCell(6).getCellTypeEnum() == CellType.STRING) {
|
|
|
996 |
String description = row.getCell(6).getStringCellValue();
|
|
|
997 |
walletHistoryModel.setDescription(description);
|
|
|
998 |
} else {
|
|
|
999 |
throw new ProfitMandiBusinessException("Invalid Transaction Type", "row number " + rowNumber,
|
|
|
1000 |
"row number " + rowNumber);
|
|
|
1001 |
}
|
|
|
1002 |
if (row.getCell(7) != null && row.getCell(7).getCellTypeEnum() == CellType.NUMERIC) {
|
|
|
1003 |
int reference = (int)row.getCell(7).getNumericCellValue();
|
|
|
1004 |
walletHistoryModel.setReference(reference);
|
|
|
1005 |
} else {
|
|
|
1006 |
throw new ProfitMandiBusinessException("Invalid Transaction Type", "row number " + rowNumber,
|
|
|
1007 |
"row number " + rowNumber);
|
|
|
1008 |
}
|
|
|
1009 |
walletHistoryModels.add(walletHistoryModel);
|
|
|
1010 |
}
|
|
|
1011 |
return walletHistoryModels;
|
|
|
1012 |
}
|
|
|
1013 |
}
|
|
|
1014 |
|
|
|
1015 |
|
|
|
1016 |
public static String toAlphabet(int number) {
|
|
|
1017 |
StringBuffer sb = new StringBuffer();
|
|
|
1018 |
boolean loop = true;
|
|
|
1019 |
while (loop) {
|
|
|
1020 |
sb.append(ALPHABETS.get(number % 26));
|
|
|
1021 |
number = number / 26;
|
|
|
1022 |
loop = number > 0;
|
|
|
1023 |
|
|
|
1024 |
}
|
|
|
1025 |
return sb.reverse().toString();
|
|
|
1026 |
}
|
|
|
1027 |
|
|
|
1028 |
|
|
|
1029 |
public static String getCellValue(Cell cell) {
|
|
|
1030 |
if (cell == null) {
|
|
|
1031 |
return "Null cell";
|
|
|
1032 |
}
|
|
|
1033 |
switch (cell.getCellTypeEnum()) {
|
|
|
1034 |
case STRING:
|
|
|
1035 |
return cell.getStringCellValue().trim();
|
|
|
1036 |
case NUMERIC:
|
|
|
1037 |
if (DateUtil.isCellDateFormatted(cell)) {
|
|
|
1038 |
return cell.getDateCellValue().toString();
|
|
|
1039 |
} else {
|
|
|
1040 |
return Double.toString(cell.getNumericCellValue());
|
|
|
1041 |
}
|
|
|
1042 |
case BOOLEAN:
|
|
|
1043 |
return Boolean.toString(cell.getBooleanCellValue());
|
|
|
1044 |
case FORMULA:
|
|
|
1045 |
return cell.getCellFormula();
|
|
|
1046 |
default:
|
|
|
1047 |
return "";
|
|
|
1048 |
}
|
|
|
1049 |
}
|
|
|
1050 |
|
|
|
1051 |
|
|
|
1052 |
public static void writePurchaseInvoicesReport(List<PurchaseInvoiceModel> purchaseInvoiceModels, OutputStream outputStream) {
|
|
|
1053 |
SXSSFWorkbook workbook = new SXSSFWorkbook();
|
|
|
1054 |
|
|
|
1055 |
// CreationHelper createHelper = workbook.getCreationHelper();
|
|
|
1056 |
|
|
|
1057 |
SXSSFSheet sheet = workbook.createSheet("PurchaseInvoicesReport");
|
|
|
1058 |
sheet.trackAllColumnsForAutoSizing();
|
|
|
1059 |
|
|
|
1060 |
Row rowHeader = sheet.createRow(0);
|
|
|
1061 |
|
|
|
1062 |
Cell cellPoDateHeader = rowHeader.createCell(1);
|
|
|
1063 |
cellPoDateHeader.setCellValue("GRN_Date");
|
|
|
1064 |
|
|
|
1065 |
Cell cellSupplierNameHeader = rowHeader.createCell(2);
|
|
|
1066 |
cellSupplierNameHeader.setCellValue("Supplier_Name");
|
|
|
1067 |
|
|
|
1068 |
Cell cellSupplierStateHeader = rowHeader.createCell(3);
|
|
|
1069 |
cellSupplierStateHeader.setCellValue("Supplier_State");
|
|
|
1070 |
|
|
|
1071 |
Cell cellSupplierGSTINHeader = rowHeader.createCell(4);
|
|
|
1072 |
cellSupplierGSTINHeader.setCellValue("Supplier_GSTIN");
|
|
|
1073 |
|
|
|
1074 |
|
|
|
1075 |
Cell cellInvoiceNumberHeader = rowHeader.createCell(5);
|
|
|
1076 |
cellInvoiceNumberHeader.setCellValue("Invoice_Number");
|
|
|
1077 |
|
|
|
1078 |
Cell cellInvoiceDateHeader = rowHeader.createCell(6);
|
|
|
1079 |
cellInvoiceDateHeader.setCellValue("Invoice_Date");
|
|
|
1080 |
|
|
|
1081 |
Cell cellSupplierWarehouseNameHeader = rowHeader.createCell(7);
|
|
|
1082 |
cellSupplierWarehouseNameHeader.setCellValue("Warehouse_Name");
|
|
|
1083 |
|
|
|
1084 |
Cell cellSupplierWarehouseIdHeader = rowHeader.createCell(8);
|
|
|
1085 |
cellSupplierWarehouseIdHeader.setCellValue("Warehouse_Id");
|
|
|
1086 |
|
|
|
1087 |
Cell cellReceivedFromHeader = rowHeader.createCell(9);
|
|
|
1088 |
cellReceivedFromHeader.setCellValue("Received_By");
|
|
|
1089 |
|
|
|
1090 |
|
|
|
1091 |
Cell cellItemIdHeader = rowHeader.createCell(10);
|
|
|
1092 |
cellItemIdHeader.setCellValue("Item_Id");
|
|
|
1093 |
|
|
|
1094 |
Cell cellBrandHeader = rowHeader.createCell(11);
|
|
|
1095 |
cellBrandHeader.setCellValue("Brand");
|
|
|
1096 |
|
|
|
1097 |
Cell cellModelNameHeader = rowHeader.createCell(12);
|
|
|
1098 |
cellModelNameHeader.setCellValue("Model_Name");
|
|
|
1099 |
|
|
|
1100 |
Cell cellModelNumberHeader = rowHeader.createCell(13);
|
|
|
1101 |
cellModelNumberHeader.setCellValue("Model_Number");
|
|
|
1102 |
|
|
|
1103 |
Cell cellColorHeader = rowHeader.createCell(14);
|
|
|
1104 |
cellColorHeader.setCellValue("Color");
|
|
|
1105 |
|
|
|
1106 |
Cell cellSystemPriceHeader = rowHeader.createCell(15);
|
|
|
1107 |
cellSystemPriceHeader.setCellValue("System_Price");
|
|
|
1108 |
|
|
|
1109 |
Cell cellUnitPriceHeader = rowHeader.createCell(16);
|
|
|
1110 |
cellUnitPriceHeader.setCellValue("Unit_Price");
|
|
|
1111 |
|
|
|
1112 |
Cell cellBasicRateHeader = rowHeader.createCell(17);
|
|
|
1113 |
cellBasicRateHeader.setCellValue("Basic_Rate");
|
|
|
1114 |
|
|
|
1115 |
Cell cellQuantityHeader = rowHeader.createCell(18);
|
|
|
1116 |
cellQuantityHeader.setCellValue("Quantity");
|
|
|
1117 |
|
|
|
1118 |
Cell cellTotalBasicValueHeader = rowHeader.createCell(19);
|
|
|
1119 |
cellTotalBasicValueHeader.setCellValue("Total_Basic_Value");
|
|
|
1120 |
|
|
|
1121 |
Cell cellSGSTHeader = rowHeader.createCell(20);
|
|
|
1122 |
cellSGSTHeader.setCellValue("SGST%");
|
|
|
1123 |
|
|
|
1124 |
Cell cellSGSTAmountHeader = rowHeader.createCell(21);
|
|
|
1125 |
cellSGSTAmountHeader.setCellValue("SGST_Amount");
|
|
|
1126 |
|
|
|
1127 |
Cell cellCGSTHeader = rowHeader.createCell(22);
|
|
|
1128 |
cellCGSTHeader.setCellValue("CGST%");
|
|
|
1129 |
|
|
|
1130 |
Cell cellCGSTAmountHeader = rowHeader.createCell(23);
|
|
|
1131 |
cellCGSTAmountHeader.setCellValue("CGST_Amount");
|
|
|
1132 |
|
|
|
1133 |
Cell cellIGSTHeader = rowHeader.createCell(24);
|
|
|
1134 |
cellIGSTHeader.setCellValue("IGST%");
|
|
|
1135 |
|
|
|
1136 |
Cell cellIGSTAmountHeader = rowHeader.createCell(25);
|
|
|
1137 |
cellIGSTAmountHeader.setCellValue("IGST_Amount");
|
|
|
1138 |
|
|
|
1139 |
Cell cellAmountWithGstHeader = rowHeader.createCell(26);
|
|
|
1140 |
cellAmountWithGstHeader.setCellValue("Total_Invoice_Value_Amount");
|
|
|
1141 |
|
|
|
1142 |
|
|
|
1143 |
Font font = workbook.createFont();
|
|
|
1144 |
CellStyle cellStyle = workbook.createCellStyle();
|
|
|
1145 |
font.setBold(true);
|
|
|
1146 |
cellStyle.setAlignment(HorizontalAlignment.CENTER);
|
|
|
1147 |
// font.setFontHeight((short)16);
|
|
|
1148 |
cellStyle.setFont(font);
|
|
|
1149 |
for (int i = 0; i < 14; i++) {
|
|
|
1150 |
if (rowHeader.getCell(i) != null) {
|
|
|
1151 |
rowHeader.getCell(i).setCellStyle(cellStyle);
|
|
|
1152 |
}
|
|
|
1153 |
}
|
|
|
1154 |
|
|
|
1155 |
for (int index = 0; index < purchaseInvoiceModels.size(); index++) {
|
|
|
1156 |
PurchaseInvoiceModel purchaseInvoiceModel = purchaseInvoiceModels.get(index);
|
|
|
1157 |
|
|
|
1158 |
Row rowValues = sheet.createRow(index + 1);
|
|
|
1159 |
|
|
|
1160 |
Cell cellPoDate = rowValues.createCell(1);
|
|
|
1161 |
cellPoDate.setCellValue(String.valueOf(purchaseInvoiceModel.getFormattedGrnDate()));
|
|
|
1162 |
|
|
|
1163 |
Cell cellSupplierName = rowValues.createCell(2);
|
|
|
1164 |
cellSupplierName.setCellValue(purchaseInvoiceModel.getSupplierName());
|
|
|
1165 |
|
|
|
1166 |
Cell cellSupplierState = rowValues.createCell(3);
|
|
|
1167 |
cellSupplierState.setCellValue(purchaseInvoiceModel.getSupplierState());
|
|
|
1168 |
|
|
|
1169 |
Cell cellSupplierGSTIN = rowValues.createCell(4);
|
|
|
1170 |
cellSupplierGSTIN.setCellValue(purchaseInvoiceModel.getSupplierGstIn());
|
|
|
1171 |
|
|
|
1172 |
Cell cellInvoiceNumber = rowValues.createCell(5);
|
|
|
1173 |
cellInvoiceNumber.setCellValue(purchaseInvoiceModel.getInvoiceNumber());
|
|
|
1174 |
|
|
|
1175 |
Cell cellInvoiceDate = rowValues.createCell(6);
|
|
|
1176 |
cellInvoiceDate.setCellValue(String.valueOf(purchaseInvoiceModel.getFormattedInvoiceDate()));
|
|
|
1177 |
|
|
|
1178 |
|
|
|
1179 |
Cell cellSupplierWarehouseName = rowValues.createCell(7);
|
|
|
1180 |
cellSupplierWarehouseName.setCellValue(purchaseInvoiceModel.getWarehouseName());
|
|
|
1181 |
|
|
|
1182 |
Cell cellSupplierWarehouseId = rowValues.createCell(8);
|
|
|
1183 |
cellSupplierWarehouseId.setCellValue(purchaseInvoiceModel.getWarehouseId());
|
|
|
1184 |
|
|
|
1185 |
Cell cellReceivedFrom = rowValues.createCell(9);
|
|
|
1186 |
cellReceivedFrom.setCellValue(purchaseInvoiceModel.getReceivedFrom());
|
|
|
1187 |
|
|
|
1188 |
Cell cellItemId = rowValues.createCell(10);
|
|
|
1189 |
cellItemId.setCellValue(purchaseInvoiceModel.getItemId());
|
|
|
1190 |
|
|
|
1191 |
Cell cellBrand = rowValues.createCell(11);
|
|
|
1192 |
cellBrand.setCellValue(purchaseInvoiceModel.getBrand());
|
|
|
1193 |
|
|
|
1194 |
Cell cellModelName = rowValues.createCell(12);
|
|
|
1195 |
cellModelName.setCellValue(purchaseInvoiceModel.getModelName());
|
|
|
1196 |
|
|
|
1197 |
Cell cellModelNumber = rowValues.createCell(13);
|
|
|
1198 |
cellModelNumber.setCellValue(purchaseInvoiceModel.getModelNumber());
|
|
|
1199 |
|
|
|
1200 |
Cell cellColor = rowValues.createCell(14);
|
|
|
1201 |
cellColor.setCellValue(purchaseInvoiceModel.getColor());
|
|
|
1202 |
|
|
|
1203 |
Cell cellSystemPrice = rowValues.createCell(15);
|
|
|
1204 |
cellSystemPrice.setCellValue(purchaseInvoiceModel.getSystemPrice());
|
|
|
1205 |
|
|
|
1206 |
Cell cellUnitPrice = rowValues.createCell(16);
|
|
|
1207 |
cellUnitPrice.setCellValue(purchaseInvoiceModel.getUnitPrice());
|
|
|
1208 |
|
|
|
1209 |
Cell cellBasicRate = rowValues.createCell(17);
|
|
|
1210 |
cellBasicRate.setCellValue(purchaseInvoiceModel.getBasicRate());
|
|
|
1211 |
|
|
|
1212 |
Cell cellQuantity = rowValues.createCell(18);
|
|
|
1213 |
cellQuantity.setCellValue(purchaseInvoiceModel.getQuantity());
|
|
|
1214 |
|
|
|
1215 |
Cell cellTotalBasicValue = rowValues.createCell(19);
|
|
|
1216 |
cellTotalBasicValue.setCellValue(purchaseInvoiceModel.getTotalBasic());
|
|
|
1217 |
|
|
|
1218 |
Cell cellSGSTPercent = rowValues.createCell(20);
|
|
|
1219 |
cellSGSTPercent.setCellValue(purchaseInvoiceModel.getSgstRate());
|
|
|
1220 |
|
|
|
1221 |
Cell cellSGSTAmount = rowValues.createCell(21);
|
|
|
1222 |
cellSGSTAmount.setCellValue(purchaseInvoiceModel.getSgstAmount());
|
|
|
1223 |
|
|
|
1224 |
Cell cellCGSTPercent = rowValues.createCell(22);
|
|
|
1225 |
cellCGSTPercent.setCellValue(purchaseInvoiceModel.getCgstRate());
|
|
|
1226 |
|
|
|
1227 |
Cell cellCGSTAmount = rowValues.createCell(23);
|
|
|
1228 |
cellCGSTAmount.setCellValue(purchaseInvoiceModel.getCgstAmount());
|
|
|
1229 |
|
|
|
1230 |
Cell cellIGSTPercent = rowValues.createCell(24);
|
|
|
1231 |
cellIGSTPercent.setCellValue(purchaseInvoiceModel.getIgstRate());
|
|
|
1232 |
|
|
|
1233 |
Cell cellIGSTAmount = rowValues.createCell(25);
|
|
|
1234 |
cellIGSTAmount.setCellValue(purchaseInvoiceModel.getIgstAmount());
|
|
|
1235 |
|
|
|
1236 |
Cell cellAmountWithGst = rowValues.createCell(26);
|
|
|
1237 |
cellAmountWithGst.setCellValue(purchaseInvoiceModel.getTotalInvoiceValueAmount());
|
|
|
1238 |
}
|
|
|
1239 |
|
|
|
1240 |
for (int index = 0; index < 14; index++) {
|
|
|
1241 |
sheet.autoSizeColumn(index);
|
|
|
1242 |
}
|
|
|
1243 |
|
|
|
1244 |
try {
|
|
|
1245 |
workbook.write(outputStream);
|
|
|
1246 |
workbook.close();
|
|
|
1247 |
} catch (IOException ioException) {
|
|
|
1248 |
LOGGER.error("Unable to generate excel file", ioException);
|
|
|
1249 |
}
|
|
|
1250 |
}
|
|
|
1251 |
|
|
|
1252 |
}
|