Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package Utilities; import PDF.PDFNumbering; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.Iterator; import java.util.Map; import java.util.logging.Level; import java.util.logging.Logger; import javax.swing.JOptionPane; import org.apache.pdfbox.exceptions.COSVisitorException; import org.apache.pdfbox.pdmodel.PDDocument; import org.apache.pdfbox.pdmodel.PDPage; import org.apache.pdfbox.pdmodel.edit.PDPageContentStream; import org.apache.pdfbox.pdmodel.font.PDType1Font; import org.apache.pdfbox.util.PDFTextStripper; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @author menglu * based on DJMS bot results, this class generates h resulting files such as forAudit, forReject, */ public class BatchInDJMSHelper { private Map<String, Map<String, String>> LEGIT_LV_MAP; private Map<String, String> INVALID_LV_MAP; private SSNDataBase SSN_DATABASE; private String ADSN; public BatchInDJMSHelper(Map<String, Map<String, String>> legitLvMap, Map<String, String> invalidLvMap, SSNDataBase ssnDb, String adsn) { LEGIT_LV_MAP = legitLvMap; INVALID_LV_MAP = invalidLvMap; SSN_DATABASE = ssnDb; String ADSN = adsn; } // given pre processed pdf file, generate two pdf files based on DJMS search results, one for audit, one for reject public void generateProcessedAndRejectPDFs(String preProcPdfFileName) throws IOException, COSVisitorException { PDDocument pdf = PDDocument.load(preProcPdfFileName); PDDocument rejectPdf = new PDDocument(); PDDocument auditPdf = new PDDocument(); String rejectPdfFileName = preProcPdfFileName.replace(".pdf", "_forReject.pdf"); String auditPdfFileName = preProcPdfFileName.replace(".pdf", "_forAudit.pdf"); int pageNum = pdf.getNumberOfPages(); // add reject page into rejectPdf PDFTextStripper pdfStripper = new PDFTextStripper(); boolean isLastReject = true; // last page status for (int i = 0; i < pageNum; i++) { PDPage page = (PDPage) pdf.getDocumentCatalog().getAllPages().get(i); int pageIndex = i + 1; pdfStripper.setStartPage(pageIndex); pdfStripper.setEndPage(pageIndex); String res = pdfStripper.getText(pdf); System.out.println(res); if (res.contains(GlobalVar.PRE_PROC_KEY_SYMBOL)) { String[] data = GlobalVar.getCtrlNumAndfullSSN(res); String ctrlNum = data[0]; String fullSSN = data[1]; System.out.println("full ssn:" + fullSSN + ". ctrl num:" + ctrlNum); if (LEGIT_LV_MAP.containsKey(fullSSN)) { System.out.println("ctrl num: " + LEGIT_LV_MAP.get(fullSSN)); } if (LEGIT_LV_MAP.containsKey(fullSSN) && LEGIT_LV_MAP.get(fullSSN).containsKey(ctrlNum)) { System.out.println("Good leave"); auditPdf.addPage(page); isLastReject = false; } else { rejectPdf.addPage(page); drawComments(rejectPdf, page, fullSSN, ctrlNum); isLastReject = true; } } else { // add the supporting documents to the last pdf file if (isLastReject) { rejectPdf.addPage(page); } else { auditPdf.addPage(page); } } } if (rejectPdf.getNumberOfPages() > 0 && auditPdf.getNumberOfPages() > 0) { auditPdf.save(auditPdfFileName); rejectPdf.save(rejectPdfFileName); JOptionPane.showMessageDialog(null, "The ready-for-aduit and the rejected leave forms are saved in *_forAudit.pdf and *_forReject.pdf, respectively."); numberPDFFile(auditPdfFileName); } else if (rejectPdf.getNumberOfPages() > 0) { rejectPdf.save(rejectPdfFileName); JOptionPane.showMessageDialog(null, "The rejected leave forms are saved in *_forReject.pdf."); } else if (auditPdf.getNumberOfPages() > 0) { auditPdf.save(auditPdfFileName); JOptionPane.showMessageDialog(null, "The ready-for-aduit leave forms are saved in *_forAduit.pdf."); numberPDFFile(auditPdfFileName); } rejectPdf.close(); auditPdf.close(); pdf.close(); } // given the ssn, ctrl num, add the comment on the given page private void drawComments(PDDocument pdf, PDPage page, String ssn, String ctrlNum) throws IOException { int errCode = getErrCodeFromMSG(ssn, ctrlNum); PDPageContentStream stream = new PDPageContentStream(pdf, page, true, false); stream.beginText(); stream.setFont(PDType1Font.HELVETICA, GlobalVar.COMMENT_FONT_SIZE); stream.moveTextPositionByAmount(GlobalVar.COMMENT_TEXT_X_POSITION, GlobalVar.COMMENT_TEXT_Y_POSITION); stream.drawString(GlobalVar.commentMap(errCode, ADSN)); stream.endText(); stream.close(); } private void numberPDFFile(String fileName) throws IOException, COSVisitorException { int response = JOptionPane.showConfirmDialog(null, "Do you want to number the pdf file? ", "Confirm", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE); if (response == JOptionPane.YES_OPTION) { new PDFNumbering(fileName); } } // given the ssn and ctrlnum, return error code. private int getErrCodeFromMSG(String ssn, String ctrlNum) { if (INVALID_LV_MAP.containsKey(ssn)) { String msg = INVALID_LV_MAP.get(ssn); return Integer.parseInt(msg.substring(msg.length() - 1)); } else if (INVALID_LV_MAP.containsKey(ctrlNum)) { String msg = INVALID_LV_MAP.get(ctrlNum); return Integer.parseInt(msg.substring(msg.length() - 1)); } else { //not here return GlobalVar.IN_CYCLE_DUPLICATION_INVALID_FIRST_FIVE_ERR; } } // given pre processed pdf file, generate ready for audit pdf based on DJMS search results public void generateReadyForAuditPDF(String preProcPdfFileName) throws IOException, COSVisitorException { PDDocument pdf = PDDocument.load(preProcPdfFileName); PDDocument auditPdf = new PDDocument(); String auditPdfFileName = preProcPdfFileName.replace(".pdf", "_forAudit.pdf"); int pageNum = pdf.getNumberOfPages(); // add reject page into rejectPdf PDFTextStripper pdfStripper = new PDFTextStripper(); for (int i = 0; i < pageNum; i++) { PDPage page = (PDPage) pdf.getDocumentCatalog().getAllPages().get(i); int pageIndex = i + 1; pdfStripper.setStartPage(pageIndex); pdfStripper.setEndPage(pageIndex); String res = pdfStripper.getText(pdf); System.out.println(res); boolean isLastReject = true; // last page status if (res.contains(GlobalVar.PRE_PROC_KEY_SYMBOL)) { String[] data = GlobalVar.getCtrlNumAndfullSSN(res); String ctrlNum = data[0]; String fullSSN = data[1]; System.out.println("full ssn:" + fullSSN + ". ctrl num:" + ctrlNum); if (LEGIT_LV_MAP.containsKey(fullSSN)) { System.out.println("ctrl num: " + LEGIT_LV_MAP.get(fullSSN)); } if (LEGIT_LV_MAP.containsKey(fullSSN) && LEGIT_LV_MAP.get(fullSSN).containsKey(ctrlNum)) { System.out.println("Good leave"); auditPdf.addPage(page); isLastReject = false; } } else { // add the supporting documents to the last pdf file if (!isLastReject) { auditPdf.addPage(page); } } } if (auditPdf.getNumberOfPages() > 0) { auditPdf.save(auditPdfFileName); JOptionPane.showMessageDialog(null, "The ready-for-aduit leave forms are saved in *_forAduit.pdf."); numberPDFFile(auditPdfFileName); } auditPdf.close(); pdf.close(); } // color transactions not in xlsx file // pre: destroy LEGIT_LV_MAP public void compareXlsxBatch(String xlsxFileName, Map<String, Map<String, String>> legitLvMap) { //Map<String, Map<String, String>> LegitLvMap = File xlsxFile = new File(xlsxFileName); try { FileInputStream fis = new FileInputStream(xlsxFile); XSSFWorkbook myWorkBook = new XSSFWorkbook(fis); //Return first sheet from the XLSX workbook XSSFSheet mySheet = myWorkBook.getSheetAt(0); legendBuilder(myWorkBook); //Get iterator to all the rows in current sheet Iterator<Row> rowIterator = mySheet.iterator(); // Traversing over each row of XLSX file if (rowIterator.hasNext()) { Row headerRow = rowIterator.next(); //skip the header row Iterator<Cell> it = headerRow.cellIterator(); int numCell = 0; // List<String> keyList = new ArrayList<>(); //keep track info of each column while (it.hasNext()) { //keyList.add(it.next().getStringCellValue()); it.next(); numCell++; } if (numCell == GlobalVar.LEAVE_TITLES_V1.length || numCell == GlobalVar.LEAVE_TITLES_V2.length) { // correct xlsx file int rowNum = 1; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //row.getRowStyle(); rowNum++; } for (int i = 1; i < rowNum; i++) { Row row = mySheet.getRow(i); foregroundColorSetUp(row, myWorkBook, numCell, legitLvMap); //check each row and update foreground color } fis.close(); FileOutputStream output; String targetFile = null; if (xlsxFileName.contains(".xlsx")) { targetFile = xlsxFileName.replace(".xlsx", "COLORED.xlsx"); } else { targetFile = xlsxFileName + "COLORED.xlsx"; } output = new FileOutputStream(targetFile); myWorkBook.write(output); output.close(); } else { JOptionPane.showMessageDialog(null, "XLSX file doesn't contain correct info!"); } } else { JOptionPane.showMessageDialog(null, "XLSX file is empty!"); System.out.println("The xlsx file is empty!"); } JOptionPane.showMessageDialog(null, "The leave roster is colored successfully. Please check *COLORED.xlsx.\n"); // case DUPLICATE_LV_ERR: return IndexedColors.YELLOW.getIndex(); // case INPROCESSING_ERR: return IndexedColors.PINK.getIndex(); // case AFTER_PCS_ERR: return IndexedColors.LAVENDER.getIndex(); // case PCS_ERR: return IndexedColors.BLUE.getIndex(); // case DUPLICATE_CTRL_NUM_ERR: return IndexedColors.DARK_YELLOW.getIndex(); // finds the work book in stance for XLSX file } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file not found exception!"); } catch (IOException ex) { JOptionPane.showMessageDialog(null, "BatchInDJMS.java: Xlsx file IO Exception!"); Logger.getLogger(BatchInDJMS.class.getName()).log(Level.SEVERE, null, ex); } } // pass in a row of V2 type xlsx, given leave data of a verified batch file stored, change the background color of a row if // a leave exists private void foregroundColorSetUpV2(Row row, XSSFWorkbook myWorkBook, Map<String, Map<String, String>> legitLvMap) { if (row != null) { Cell ssnCell = row.getCell(GlobalVar.FULL_SSN_CELL_INDEX_V2); //DataFormatter df = new DataFormatter(); // String lastFour = df.formatCellValue(ssnCell); //return ***-**-**** DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); // String lastFour = df.formatCellValue(ssnCell); String ssn = GlobalVar.fullSSNgenerator(df.formatCellValue(ssnCell)); //System.out.println(lastName); if (ssn != null) { Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V2); // String ctrlNumString = ctrlNumCell.getStringCellValue(); Cell soDateCell = row.getCell(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V2); String soDate = df.formatCellValue(soDateCell); colorLeaves(ssn, ctrlNumCell, soDate, myWorkBook, legitLvMap); } } } //based on global variable VALID_LV_MAP and INVALID_LV_MAP, color the ctrlNum cell differently private void colorLeaves(String ssn, Cell ctrlNumCell, String soDate, XSSFWorkbook myWorkBook, Map<String, Map<String, String>> legitLvMap) { String ctrlNumString = null; if (ctrlNumCell != null) { ctrlNumString = ctrlNumCell.getStringCellValue().trim(); } //String ctrlNumString = // color valid leaves if (legitLvMap != null && legitLvMap.containsKey(ssn)) { // Map< SSN, Map<ctrlNum, signOutdate>> Map<String, String> value = legitLvMap.get(ssn); if (value.containsKey(ctrlNumString) && value.get(ctrlNumString).equals(soDate)) { CellStyle style = GlobalVar.createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); value.remove(ctrlNumString); return; // colored the cell } } // end of coloring valid leaves // color invalid leaves if (INVALID_LV_MAP.containsKey(ssn)) { String msg = INVALID_LV_MAP.get(ssn); colorInvalidLeave(msg, myWorkBook, ctrlNumCell); // might contain more than one bad leaves because of ssn } else if (INVALID_LV_MAP.containsKey(ctrlNumString)) { String msg = INVALID_LV_MAP.get(ctrlNumString); //INVALID_LV_MAP.remove(ctrlNumString); colorInvalidLeave(msg, myWorkBook, ctrlNumCell); } } //given error code and color the xlsx cell differently. private void colorInvalidLeave(String msg, XSSFWorkbook myWorkBook, Cell ctrlNumCell) { int errCode = Integer.parseInt(msg.substring(msg.length() - 1)); System.out.println("BatchInDJMS.java: error code is " + errCode); CellStyle style = GlobalVar.createStandardStyle(myWorkBook); style.setFillForegroundColor(colorErrorMap(errCode)); //color map conversion style.setFillPattern(CellStyle.SOLID_FOREGROUND); // cellStyle = ctrlNumCell. ctrlNumCell.setCellStyle(style); } // given the error code return the correspondance color private short colorErrorMap(int errorCode) { switch (errorCode) { case GlobalVar.OVERLAP_LV_ERR: return GlobalVar.OVERLAP_LV_COLOR; case GlobalVar.DUPLICATE_LV_ERR: return GlobalVar.DUPLICATE_LV_COLOR; case GlobalVar.ETS_ERR: return GlobalVar.ETS_STATUS_COLOR; case GlobalVar.BAD_STATUS_ERR: return GlobalVar.BAD_STATUS_COLOR; case GlobalVar.WRONG_SSN_ERR: return GlobalVar.WRONG_SSN_COLOR; case GlobalVar.DUPLICATE_CTRL_NUM_ERR: return GlobalVar.DUPLICATE_CTRL_NUM_COLOR; case GlobalVar.INPROCESSING_ERR: return GlobalVar.INPROCESSING_COLOR; case GlobalVar.AFTER_PCS_ERR: return GlobalVar.AFTER_PCS_COLOR; //case PCS_ERR: return IndexedColors.RED.getIndex(); default: return IndexedColors.BLACK.getIndex(); } } // pass in a row of V1 type xlsx, given leave data of a verified batch file stored, change the background color of a row if // a leave exists private void foregroundColorSetUpV1(Row row, XSSFWorkbook myWorkBook, Map<String, Map<String, String>> legitLvMap) { if (row != null) { Cell ssnCell = row.getCell(GlobalVar.LAST4_CELL_INDEX_V1); //DataFormatter df = new DataFormatter(); // String lastFour = df.formatCellValue(ssnCell); //return ***-**-**** DataFormatter df = new DataFormatter(); //SimpleDateFormat fmt = new SimpleDateFormat("yyMMdd"); // String lastFour = df.formatCellValue(ssnCell); String lastFour = GlobalVar.last4Generator(df.formatCellValue(ssnCell)); Cell lastNameCell = row.getCell(GlobalVar.LAST_NAME_CELL_INDEX_V1); String lastName = null; if (lastNameCell != null) { lastName = lastNameCell.getStringCellValue(); } System.out.println(lastName); if (SSN_DATABASE != null) { String ssn = SSN_DATABASE.getSSN(lastName, lastFour); if (ssn != null) { Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V1); // String ctrlNumString = ctrlNumCell.getStringCellValue(); Cell soDateCell = row.getCell(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1); String soDate = df.formatCellValue(soDateCell); colorLeaves(ssn, ctrlNumCell, soDate, myWorkBook, legitLvMap); } } } } private void foregroundColorSetUp(Row row, XSSFWorkbook myWorkBook, int numCell, Map<String, Map<String, String>> legitLvMap) { if (row != null) { if (numCell == GlobalVar.LEAVE_TITLES_V1.length) { foregroundColorSetUpV1(row, myWorkBook, legitLvMap); } else if (numCell == GlobalVar.LEAVE_TITLES_V2.length) { foregroundColorSetUpV2(row, myWorkBook, legitLvMap); } } } private void legendBuilder(XSSFWorkbook myWorkBook) { XSSFSheet mySheet = myWorkBook.getSheetAt(0); Row row1; Row row2; Row row3; Row row4; System.out.println("BatchInDJMSHelper.java: Line number in xlsx" + mySheet.getPhysicalNumberOfRows()); // Create a row and put some cells in it. if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y) { row1 = mySheet.getRow(GlobalVar.LEGEND_Y); } else { row1 = mySheet.createRow(GlobalVar.LEGEND_Y); } if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y + 1) { row2 = mySheet.getRow(GlobalVar.LEGEND_Y + 1); } else { row2 = mySheet.createRow(GlobalVar.LEGEND_Y + 1); } if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y + 2) { row3 = mySheet.getRow(GlobalVar.LEGEND_Y + 2); } else { row3 = mySheet.createRow(GlobalVar.LEGEND_Y + 2); } if (mySheet.getPhysicalNumberOfRows() > GlobalVar.LEGEND_Y + 3) { row4 = mySheet.getRow(GlobalVar.LEGEND_Y + 3); } else { row4 = mySheet.createRow(GlobalVar.LEGEND_Y + 3); } // Row row2 = mySheet.getRow(GlobalVar.LEGEND_Y + 1); // Row row3 = mySheet.getRow(GlobalVar.LEGEND_Y + 2); // Row row4 = mySheet.getRow(GlobalVar.LEGEND_Y + 3); int col1 = GlobalVar.LEGEND_X; int col2 = GlobalVar.LEGEND_X + 1; int col3 = GlobalVar.LEGEND_X + 2; int col4 = GlobalVar.LEGEND_X + 3; //// row 1 CellStyle style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.ETS_STATUS_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); Cell cell = row1.createCell(col1); cell.setCellValue("ETS"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.BAD_STATUS_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row1.createCell(col2); cell.setCellValue("Bad"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.OVERLAP_LV_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row1.createCell(col3); cell.setCellValue("Overlap Lv"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.DUPLICATE_LV_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row1.createCell(col4); cell.setCellValue("Duplicate Lv"); cell.setCellStyle(style); // case INPROCESSING_ERR: return INPROCESSING_COLOR; // case AFTER_PCS_ERR: return AFTER_PCS_COLOR; //// row 2 style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.WRONG_SSN_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row2.createCell(col1); cell.setCellValue("Wrong SSN"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.DUPLICATE_CTRL_NUM_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row2.createCell(col2); cell.setCellValue("Duplicate CtrlNum"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.INPROCESSING_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row2.createCell(col3); cell.setCellValue("Inprocessing"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.AFTER_PCS_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row2.createCell(col4); cell.setCellValue("After PCS"); cell.setCellStyle(style); // row3 style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.IN_CYCLE_DUPLICATE_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row3.createCell(col1); cell.setCellValue("In-cycle duplicates/Invalid first five"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(GlobalVar.AUDITOR_DELETED_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row3.createCell(col2); cell.setCellValue("Auditor deleted"); cell.setCellStyle(style); } }