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 java.awt.AWTException; import java.awt.Robot; import java.awt.Toolkit; import java.awt.datatransfer.Clipboard; import java.awt.datatransfer.DataFlavor; import java.awt.datatransfer.Transferable; import java.awt.datatransfer.UnsupportedFlavorException; import java.awt.event.KeyEvent; import java.awt.event.KeyListener; import java.io.*; import static java.lang.System.exit; import java.util.*; import java.util.logging.Level; import java.util.logging.Logger; import javax.swing.JOptionPane; 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 SPC Cui. This class checks the leaves on the batch file in DJMS * and delete the transactions that are posted before or F9 */ public class DebtMgmtBatchInDJMS implements KeyListener { private Map<String, Integer> SSN_MAP = new HashMap<>(); // key = ssn, value: status private Map<String, String> SSN_NAME_MAP = new HashMap<>(); private final int LEGEND_X = 3; private final int LEGEND_Y = 3; private final int INITIAL_MSG = -999; private final int ETS_STATUS_MSG = 999; private final int BAD_STATUS_MSG = 998; //ETS, AWOL, DFR, Suspend, etc private final int SUSPENDED_STATUS_MSG = 997; //Q8 private final int PRA_STATUS_MSG = 996; private final int FULLY_COLLRECTED_MSG = 4; private final int QUESTIONABLE_MSG = 5; private final int PCS_RELEASED_MSG = 910; private final int WOUNDED_WARRIOR_MSG = 995; // private final int NEW_ENT_MSG = 7; private final int DEBT_LETTER_MSG = 900; private final int COLLECTING_MSG = 8; // private final int QUESTIONABLE_MSG = 5; // private final int SSN_CELL_INDEX = 0; private final int TITLE_LEN = 2; private final int SSN_INDEX = 0; private final int LAST_NAME_INDEX = 1; private final int FID_INDEX = 3; private final double COLLECTING_THRESHOLD = 2000.0; private final short ETS_STATUS_COLOR = IndexedColors.GREY_50_PERCENT.getIndex(); private final short BAD_STATUS_COLOR = IndexedColors.ORANGE.getIndex(); //ETS, AWOL, DFR, Suspend, etc private final short SUSPENDED_STATUS_COLOR = IndexedColors.AQUA.getIndex(); //Q8 private final short PRA_STATUS_COLOR = IndexedColors.CORNFLOWER_BLUE.getIndex(); private final short FULLY_COLLRECTED_COLOR = IndexedColors.GREEN.getIndex(); private final short WOUNDED_WARRIOR_COLOR = IndexedColors.CORAL.getIndex(); // private final short NEW_ENT_COLOR = IndexedColors.TAN.getIndex(); private final short DEBT_LETTER_COLOR = IndexedColors.RED.getIndex(); private final short COLLECTING_COLOR = IndexedColors.YELLOW.getIndex(); private final short QUESTIONABLE_COLOR = IndexedColors.PINK.getIndex(); private final short PCS_COLOR = IndexedColors.ORCHID.getIndex(); private boolean IN_TRNS_STATUS = false; //in a transaction status private static Robot ROBOT; private static Clipboard CLIP_BOARD; //private final String sourceBatchFileName; // original batch file name private int SPEED2 = GlobalVar.SPEED2; private String VERIFIED_BATCH_FILE_NAME = null; private String TRANS = ""; // Step 1; constructor public DebtMgmtBatchInDJMS(String xlsxFileName) throws FileNotFoundException, AWTException, UnsupportedFlavorException, IOException, InterruptedException { CLIP_BOARD = Toolkit.getDefaultToolkit().getSystemClipboard(); ROBOT = new Robot(); JOptionPane.showMessageDialog(null, "Are you ready to have DJMS come to the front? \n" + " Make sure FID LGO, DQO and PU are prefilled."); //ROBOT.delay(GlobalVar.SPEED_DJMS);//wait for the user to focus on DJMS Thread.sleep(GlobalVar.SPEED_DJMS); buildSSNMap(xlsxFileName); } // Step 2: check ssn in DJMS and find it status public void statusFinderInDJMS() throws UnsupportedFlavorException, IOException, AWTException, InterruptedException { Thread.sleep(GlobalVar.SPEED_DJMS); for (String ssn : SSN_MAP.keySet()) { //lineCount++; System.out.println("Now processing: " + ssn); keyInSSNandSB(ssn, ROBOT); copyDJMSContent(); Transferable contents = CLIP_BOARD.getContents(null); String res = (String) contents.getTransferData(DataFlavor.stringFlavor); if (isETSStatus(res)) { SSN_MAP.put(ssn, ETS_STATUS_MSG); } else if (isBadStatus(res)) { //ssn exists and in a good status SSN_MAP.put(ssn, BAD_STATUS_MSG); //System.out.println("BatchInDJMS.java: active status."); } else if (isWoundedWarrior(res)) { SSN_MAP.put(ssn, WOUNDED_WARRIOR_MSG); } else if (isPRAAccount(res)) { SSN_MAP.put(ssn, PRA_STATUS_MSG); } else if (isDebtLetterNeeded(res)) { SSN_MAP.put(ssn, DEBT_LETTER_MSG); } else if (isDQFullyCollectedThisMonth(res)) { SSN_MAP.put(ssn, FULLY_COLLRECTED_MSG); } else if (isNewEntitlement(res)) { SSN_MAP.put(ssn, NEW_ENT_MSG); } else if (isSuspendStatus(res)) { SSN_MAP.put(ssn, SUSPENDED_STATUS_MSG); } else { processClipboardContent(res, ssn, contents); } // } else if (isCollecting(res)){ // SSN_MAP.put(ssn, COLLECTING_MSG); // } else{ // getDQfromCurrentResult(res, ssn); // //SSN_MAP.put(ssn, QUESTIONABLE_MSG); // } } } // given ssn and clipboard content, analyze leaves posted before, find invalid leaves private void processClipboardContent(String res, String ssn, Transferable contents) throws InterruptedException, UnsupportedFlavorException, IOException { //extractFirst5(res, ssn); // extract first5 char of the given ssn and content on clipboard and update SSN_NAME_MAP processTranfromCurrentResult(res, ssn); // put bad leave dates in the list while (continue2NextPage(res)) { move2nextpage(); copyDJMSContent(); contents = CLIP_BOARD.getContents(null); res = (String) contents.getTransferData(DataFlavor.stringFlavor); processTranfromCurrentResult(res, ssn); } } // // move to next screen in DJMS private void move2nextpage() throws InterruptedException { ROBOT.keyPress(KeyEvent.VK_F8); ROBOT.keyRelease(KeyEvent.VK_F8); //ROBOT.delay(SPEED2); Thread.sleep(SPEED2); } // // // given the DJMS screenshot, return true if there is next page, // // return false otherwise private boolean continue2NextPage(String res) { //String[] lines = res.split("MMPA RECORD IS NOT PRESENT"); if (!res.contains("MMPA RECORD IS NOT PRESENT")) { String[] lines = res.split("\n"); if (lines.length > GlobalVar.PAGE_NUM_LINE) { lines = res.split("\n"); String line = lines[GlobalVar.PAGE_NUM_LINE - 1].trim(); //zero page. line = line.replaceAll("[ ]+", " "); String[] texts = line.split(" "); // System.out.println(line); int len = texts.length; String totalPage = texts[len - 1]; String currentPage = texts[len - 3]; return !totalPage.equals(currentPage); } else { return false; } } return false; } // given the clipboard content, find separate res into transactions private void processTranfromCurrentResult(String res, String ssn) { String[] lines = res.split("\n"); //Map<String, List<String>> leaves = LEAVE_DATA.get(ssn); for (String line : lines) { //line = line.trim(); if (IN_TRNS_STATUS && !isNewTranTriggered(line)) { TRANS += " " + line; } else if (!IN_TRNS_STATUS && isNewTranTriggered(line)) { IN_TRNS_STATUS = true; TRANS = line; } else if (IN_TRNS_STATUS && isNewTranTriggered(line)) { // a new tran starts IN_TRNS_STATUS = true; System.out.println("Current transaction is:\n" + TRANS); // System.out.println(); processDQStatusfromTransaction(TRANS, ssn); //process DQ transactions TRANS = line; } } } // // get amount // private double getDedtnCMfromTransaction(String transaction, // String ssn) { // if(transaction != null) { // if (transaction.contains("ACTN Q8")) { // SSN_MAP.put(ssn, SUSPENDED_MSG); // return -1.0; // } else if (transaction.contains("FORMAT-ID MD")) { // SSN_MAP.put(ssn, MD_STATUS_MSG); // return -1.0; // } else { // transaction = transaction.replaceAll("DEDTN-CM ", "@@@"); // transaction = transaction.replaceAll("DEDTN-NM ", "@@@"); // //transaction = transaction.replaceAll("AUTH-NR ", "@@@"); // /// transaction = transaction.replaceAll("TYPE", "@@@"); // String[] DJMSinfo = transaction.split("@@@"); //leave posted before // // // System.out.println(Arrays.toString(info)); // if(DJMSinfo.length > 1){ // return Double.parseDouble(DJMSinfo[1]); // } else { // System.out.println("Shall never be here"); // return -1.0; // } // } // } // System.out.println("Shall never be here"); // return -1.0; // } // return true if tran start with a FID, otherwise, return false private static boolean isNewTranTriggered(String line) { String fid = null; if (line.length() > 4) { fid = line.substring(0, 4); //System.out.println(fid); return (!fid.equals(" ")); } return false; } // Alt + e + y to copy screen content private void copyDJMSContent() throws InterruptedException { //ROBOT.delay(SPEED2);//wait for the user to focus on DJMS Thread.sleep(SPEED2); ROBOT.keyPress(KeyEvent.VK_ALT); ROBOT.keyRelease(KeyEvent.VK_ALT); // ROBOT.delay(SPEED2);//wait for the user to focus on DJMS Thread.sleep(SPEED2); ROBOT.keyPress(KeyEvent.VK_E); ROBOT.keyRelease(KeyEvent.VK_E); // ROBOT.delay(SPEED2);//wait for the user to focus on DJMS Thread.sleep(SPEED2); ROBOT.keyPress(KeyEvent.VK_Y); ROBOT.keyRelease(KeyEvent.VK_Y); // ROBOT.delay(SPEED2);//wait for the user to focus on DJMS Thread.sleep(SPEED2); //System.out.println("DJMS copy"); } // make sure // check SSN and SB line in DJMS private void keyInSSNandSB(String SSN, Robot r) throws InterruptedException { for (int i = 0; i < 9; i++) { char digit = SSN.charAt(i); //System.out.println(digit); r.keyPress(digitKeyEventValue(digit)); r.keyRelease(digitKeyEventValue(digit)); r.delay(GlobalVar.SPEED1); } r.keyPress(KeyEvent.VK_F8); r.keyRelease(KeyEvent.VK_F8); //r.delay(SPEED2);//wait for the user to focus on DJMS Thread.sleep(SPEED2); } private char letterKeyEventValue(char letter) { switch (letter) { case 'a': return KeyEvent.VK_A; case 'b': return KeyEvent.VK_B; case 'c': return KeyEvent.VK_C; case 'd': return KeyEvent.VK_D; case 'e': return KeyEvent.VK_E; case 'f': return KeyEvent.VK_F; case 'g': return KeyEvent.VK_G; case 'h': return KeyEvent.VK_H; case 'i': return KeyEvent.VK_I; case 'j': return KeyEvent.VK_J; case 'k': return KeyEvent.VK_K; case 'l': return KeyEvent.VK_L; case 'm': return KeyEvent.VK_M; case 'n': return KeyEvent.VK_N; case 'o': return KeyEvent.VK_O; case 'p': return KeyEvent.VK_P; case 'q': return KeyEvent.VK_Q; case 'r': return KeyEvent.VK_R; case 's': return KeyEvent.VK_S; case 't': return KeyEvent.VK_T; case 'u': return KeyEvent.VK_U; case 'v': return KeyEvent.VK_V; case 'w': return KeyEvent.VK_W; case 'x': return KeyEvent.VK_X; case 'y': return KeyEvent.VK_Y; default: return KeyEvent.VK_Z; //default: return 0; } } private char digitKeyEventValue(char digit) { switch (digit) { case '0': return KeyEvent.VK_0; case '1': return KeyEvent.VK_1; case '2': return KeyEvent.VK_2; case '3': return KeyEvent.VK_3; case '4': return KeyEvent.VK_4; case '5': return KeyEvent.VK_5; case '6': return KeyEvent.VK_6; case '7': return KeyEvent.VK_7; case '8': return KeyEvent.VK_8; default: return KeyEvent.VK_9; } } //return false if res contains RY or given content doesn't contain 4830 or SM is not Presnt on Duty private boolean isActiveStatus(String res) { String adsn = "LC:" + GlobalVar.JBLM_LC; return (!res.contains("PACIDN RY") && res.contains(adsn) && res.contains("SA:A")); } //return true if res contains PACIDN RY private boolean isETSStatus(String res) { return (res.contains("PACIDN RY") || res.contains("SA:V")); } // Return true if DQ line is not open, return false otherwise. private boolean isNewEntitlement(String res) { return (res.contains("DQ O ENTRY NO DATA FOUND") || (!res.contains("DQ INDEBT MIL PAY/ALW") && !res.contains("DQ-INDEBT MIL PAY/ALW"))); //new entitlements } // return true if K, J, P, T status private boolean isBadStatus(String res) { return (!res.contains("SA:A") && !res.contains("SA:V") && !res.contains("SA:T") && !res.contains("SA:H")); } private boolean isSuspendStatus(String res) { return (res.contains("SA:T")); } private boolean isPRAAccount(String res) { return (res.contains("PACIDN FI")); // PRA } private boolean isWoundedWarrior(String res) { return (res.contains("PACIDN IW")); // wounded warrior } // Return true if DQ line is not open, return false otherwise. private boolean isDQFullyCollectedThisMonth(String res) { return (res.contains("DQ-INDEBT MIL PAY/ALW") && !res.contains("DQ INDEBT MIL PAY/ALW")); // just closed this month } // pre: p private boolean isCollecting(String res) { if (res.contains("DQ INDEBT MIL PAY/ALW") && (!res.contains("ACTN Q8") && !res.contains("ACTN 18"))) { res = res.replaceAll("DEDTN-CM", "@@@@"); res = res.replaceAll("DEDTN-NM", "@@@@"); String[] date = res.split("@@@@"); if (date.length > 1) { String amtString = date[1].trim(); amtString = amtString.replace(",", ""); double amount = Double.parseDouble(amtString); System.out.println("Collecting amount:" + amount); if (amount < COLLECTING_THRESHOLD) { return true; } else { // questionable } } } return false; } // pre: given a transaction, // post: return true if the open transaction contains PCS debt private boolean isPCSDebtReleasedNow(String DQtran) { return DQtran.contains("DQ INDEBT MIL PAY/ALW") && DQtran.contains("FORMAT-ID SG"); } private boolean isDebtLetterNeeded(String res) { if (res.contains("DQ INDEBT MIL PAY/ALW") && (res.contains("ACTN Q8") || res.contains("ACTN 18"))) { res = res.replaceAll("SPEC-INDEBT", "@@@@"); res = res.replaceAll("BAL-DUE-CM", "@@@@"); String[] date = res.split("@@@@"); System.out.println("Date length:" + date.length); if (date.length > 1) { String amtString = date[1].trim(); amtString = amtString.replaceAll(",", ""); double amount = Double.parseDouble(amtString); System.out.println("Amount: " + amount); if (amount > 50.0) { return true; } else { // questionable } } } return false; } private void processDQStatusfromTransaction(String trans, String ssn) { if (isPCSDebtReleasedNow(trans)) { if (SSN_MAP.containsKey(ssn) && SSN_MAP.get(ssn) < PCS_RELEASED_MSG) { SSN_MAP.put(ssn, PCS_RELEASED_MSG); } } else if (isDebtLetterNeeded(trans)) { if (SSN_MAP.containsKey(ssn) && SSN_MAP.get(ssn) < DEBT_LETTER_MSG) { SSN_MAP.put(ssn, DEBT_LETTER_MSG); } } else if (isCollecting(trans)) { if (SSN_MAP.containsKey(ssn) && SSN_MAP.get(ssn) < COLLECTING_MSG) { SSN_MAP.put(ssn, COLLECTING_MSG); } } else { if (SSN_MAP.containsKey(ssn) && SSN_MAP.get(ssn) < QUESTIONABLE_MSG) { SSN_MAP.put(ssn, QUESTIONABLE_MSG); } } } // private boolean isWrongSSNStatus(String res) { // return (res.contains("MMPA RECORD IS NOT PRESENT")) ; // } // // private boolean isDebtOverTwoMonth(String res) { // MyDate today = new MyDate(); // if(res.contains("START-OPAYMT") && res.contains("STOP-OPAYMT")){ // res.replace("START-OPAYMT", "@@@"); // res.replace("STOP-OPAYMT", "@@@"); // String[] dates = res.split("@@@"); // System.out.println("Overpayment start date"); // String opayDt = dates[1].trim(); // // MyDate opayDate = new MyDate(opayDt); // return (opayDate.getDaysDiff(today) > 60); // } // return false; // } // // // Return true if DQ line is open, return false otherwise. // private boolean isDQOpen(String res) { // return (res.contains("DQ INDEBT MIL PAY/ALW")) ; // } // // // Return true if debt caused by rank reduction, return false otherwise. // private boolean isMDStatus(String res) { // return (res.contains("FORMAT-ID MD")) ; // } // color transactions not in xlsx file public void compareXlsxBatch(String xlsxFileName) { 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 = 0; i < rowNum; i++) { Row row = mySheet.getRow(i); foregroundColorSetUp(row, myWorkBook); //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(DebtMgmtBatchInDJMS.class.getName()).log(Level.SEVERE, null, ex); } } // extract ssn from given xlsx file private void buildSSNMap(String xlsxFileName) { 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); //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; DataFormatter df = new DataFormatter(); //for ssn // if (numCell == TITLE_LEN){ // correct xlsx file int rowNum = 1; while (rowIterator.hasNext()) { Row row = rowIterator.next(); //row.getRowStyle(); Cell cell = row.getCell(SSN_INDEX); String ssn = df.formatCellValue(cell); //return ***-**-**** ssn = GlobalVar.fullSSNgenerator(ssn); SSN_MAP.put(ssn, INITIAL_MSG); //initial ssn map . value is for the status } // } else { // JOptionPane.showMessageDialog(null, "Invalid 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(DebtMgmtBatchInDJMS.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 foregroundColorSetUp(Row row, XSSFWorkbook myWorkBook) { if (row != null) { Cell ssnCell = row.getCell(SSN_CELL_INDEX); DataFormatter df = new DataFormatter(); String ssn = GlobalVar.fullSSNgenerator(df.formatCellValue(ssnCell)); if (ssn != null) { colorTransactions(ssn, ssnCell, myWorkBook); } } } //based on global variable VALID_LV_MAP and INVALID_LV_MAP, color the ctrlNum cell differently private void colorTransactions(String ssn, Cell ssnCell, XSSFWorkbook myWorkBook) { // color valid leaves if (SSN_MAP.containsKey(ssn)) { // Map< SSN, Map<ctrlNum, signOutdate>> CellStyle style = createStandardStyle(myWorkBook); int msgCode = SSN_MAP.get(ssn); System.out.println("MSG Code: " + msgCode); style.setFillForegroundColor(colorMsgMap(msgCode)); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ssnCell.setCellStyle(style); } } // given the error code return the correspondance color private short colorMsgMap(int errorCode) { switch (errorCode) { case ETS_STATUS_MSG: return (short) ETS_STATUS_COLOR; case BAD_STATUS_MSG: return (short) BAD_STATUS_COLOR; case FULLY_COLLRECTED_MSG: return (short) FULLY_COLLRECTED_COLOR; case PRA_STATUS_MSG: return (short) PRA_STATUS_COLOR; case NEW_ENT_MSG: return (short) NEW_ENT_COLOR; case WOUNDED_WARRIOR_MSG: return (short) WOUNDED_WARRIOR_COLOR; case SUSPENDED_STATUS_MSG: return (short) SUSPENDED_STATUS_COLOR; case DEBT_LETTER_MSG: return (short) DEBT_LETTER_COLOR; case COLLECTING_MSG: return (short) COLLECTING_COLOR; case QUESTIONABLE_MSG: return (short) QUESTIONABLE_COLOR; case PCS_RELEASED_MSG: return (short) PCS_COLOR; //case PCS_ERR: return IndexedColors.RED.getIndex(); default: return IndexedColors.WHITE.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 foregroundColorSetUp(Row row, XSSFWorkbook myWorkBook) { // if (row != null) { // Cell ssnCell = row.getCell(SSN_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); // // } // } // create a standard style for given workbook private CellStyle createStandardStyle(XSSFWorkbook myWorkBook) { CellStyle style = myWorkBook.createCellStyle(); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setWrapText(true); return style; } @Override public void keyTyped(KeyEvent e) { throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. } @Override public void keyPressed(KeyEvent e) { // throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. if (e.getKeyCode() == KeyEvent.VK_ENTER) { // STOP = true; System.out.println("Terminated"); exit(1); } } @Override public void keyReleased(KeyEvent e) { // throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates. } public void output2NotePad() throws FileNotFoundException { File file = new File(GlobalVar.SECURE_FOLDER_NAME); GlobalVar.dirMake(file); PrintStream outputWallerHall = new PrintStream(new File(GlobalVar.SECURE_FOLDER_NAME + "/Waller Hall.txt")); PrintStream outputPRA = new PrintStream(new File(GlobalVar.SECURE_FOLDER_NAME + "/PRA.txt")); PrintStream outputWW = new PrintStream(new File(GlobalVar.SECURE_FOLDER_NAME + "/WoundedWarrior.txt")); PrintStream outputBadStatus = new PrintStream(new File(GlobalVar.SECURE_FOLDER_NAME + "/BadStatus.txt")); PrintStream outputTStatus = new PrintStream(new File(GlobalVar.SECURE_FOLDER_NAME + "/TStatus.txt")); PrintStream outputDebtLetter = new PrintStream(new File(GlobalVar.SECURE_FOLDER_NAME + "/DebtLetter.txt")); PrintStream outputQuestionable = new PrintStream( new File(GlobalVar.SECURE_FOLDER_NAME + "/Questionable.txt")); PrintStream outputFullyCollected = new PrintStream( new File(GlobalVar.SECURE_FOLDER_NAME + "/FullyCollected.txt")); PrintStream outputNewEnt = new PrintStream(new File(GlobalVar.SECURE_FOLDER_NAME + "/NewEntitlements.txt")); PrintStream outputCollecting = new PrintStream(new File(GlobalVar.SECURE_FOLDER_NAME + "/Collecting.txt")); PrintStream outputPCSdebt = new PrintStream(new File(GlobalVar.SECURE_FOLDER_NAME + "/PCSdebt.txt")); for (String ssn : SSN_MAP.keySet()) { int msg = SSN_MAP.get(ssn); switch (msg) { case ETS_STATUS_MSG: outputWallerHall.println(ssn); break; case BAD_STATUS_MSG: outputBadStatus.println(ssn); break; case FULLY_COLLRECTED_MSG: outputFullyCollected.println(ssn); break; case PRA_STATUS_MSG: outputPRA.println(ssn); break; case NEW_ENT_MSG: outputNewEnt.println(ssn); break; case WOUNDED_WARRIOR_MSG: outputWW.println(ssn); break; case SUSPENDED_STATUS_MSG: outputTStatus.println(ssn); break; case DEBT_LETTER_MSG: outputDebtLetter.println(ssn); break; case QUESTIONABLE_MSG: outputQuestionable.println(ssn); break; case COLLECTING_MSG: outputCollecting.println(ssn); break; case PCS_RELEASED_MSG: outputPCSdebt.println(ssn); break; } } outputWallerHall.close(); outputPRA.close(); outputWW.close(); outputBadStatus.close(); outputTStatus.close(); outputDebtLetter.close(); outputQuestionable.close(); outputFullyCollected.close(); outputNewEnt.close(); outputCollecting.close(); outputPCSdebt.close(); } // private void extractFirst5(String res, String ssn) { // if (!res.contains("MMPA RECORD IS NOT PRESENT")) { // String[] lines = res.split("\n"); // if(lines.length > GlobalVar.PAGE_NUM_LINE){ // lines = res.split("\n"); // String line = lines[GlobalVar.PAGE_NUM_LINE - 1].trim(); //zero page. // //System.out.println("BatchInDIMS.java: this line of first 5 is " + line); // // 586113099 TAULU 36 LGO LC SB 01 OF 01 // line = line.replaceAll(ssn, ""); // line = line.trim(); // String first5 = line.substring(0, 5); // // System.out.println("BatchInDIMS.java: first 5 is " + first5); // if (!SSN_NAME_MAP.containsKey(ssn)){ // SSN_NAME_MAP.put(ssn, first5); // }else{ // System.out.println("BatchInDJMS.java: DEVELOPERS WARNING: shall not be here!" + ssn); // } // } // } // } // // pre: not Active in 4830. // // given the clipboard content, check if it is in PCS status // private boolean isPCSed(String res) { // String ADSN = "LC:" + GlobalVar.JBLM_LC; // not contain LC:4830 SA:A // return ((res.contains("SA:H") || res.contains("LC:9999")) // || (res.contains("SA:A") && !res.contains(ADSN))); //stationed in JBLM before, in another duty station now. // } // //never station in JBLM // private boolean needsInProcessing(String res) { // String ADSN = "ADSN " + GlobalVar.JBLM_LC; // not contain ADSN 4830 // return ((res.contains("SA:H") || res.contains("LC:9999")) // && (res.contains("SA:A") && !res.contains(ADSN))); //stationed in JBLM before, in another duty station now. // } private void legendBuilder(XSSFWorkbook myWorkBook) { XSSFSheet mySheet = myWorkBook.getSheetAt(0); // Create a row and put some cells in it. Row row1 = mySheet.getRow(LEGEND_Y); Row row2 = mySheet.getRow(LEGEND_Y + 1); Row row3 = mySheet.getRow(LEGEND_Y + 2); Row row4 = mySheet.getRow(LEGEND_Y + 3); int col1 = LEGEND_X; int col2 = LEGEND_X + 1; int col3 = LEGEND_X + 2; int col4 = LEGEND_X + 3; //// row 1 CellStyle style = myWorkBook.createCellStyle(); style.setFillForegroundColor(ETS_STATUS_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); Cell cell = row1.createCell(col1); cell.setCellValue("ETS"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(BAD_STATUS_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row1.createCell(col2); cell.setCellValue("Bad"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(SUSPENDED_STATUS_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row1.createCell(col3); cell.setCellValue("Suspended"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(PRA_STATUS_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row1.createCell(col4); cell.setCellValue("PRA"); cell.setCellStyle(style); //// row 2 style = myWorkBook.createCellStyle(); style.setFillForegroundColor(FULLY_COLLRECTED_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row2.createCell(col1); cell.setCellValue("Fully Collected"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(WOUNDED_WARRIOR_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row2.createCell(col2); cell.setCellValue("WW"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(NEW_ENT_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row2.createCell(col3); cell.setCellValue("New Entitlement"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(DEBT_LETTER_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row2.createCell(col4); cell.setCellValue("Debt Letter"); cell.setCellStyle(style); //// row 3 style = myWorkBook.createCellStyle(); style.setFillForegroundColor(COLLECTING_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row3.createCell(col1); cell.setCellValue("Collecting"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(PCS_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row3.createCell(col2); cell.setCellValue("PCS"); cell.setCellStyle(style); style = myWorkBook.createCellStyle(); style.setFillForegroundColor(QUESTIONABLE_COLOR); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell = row3.createCell(col3); cell.setCellValue("Questionable"); cell.setCellStyle(style); } public void printSSNmap() { System.out.println(SSN_MAP); } }