List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet iterator
@Override
public Iterator<Row> iterator()
From source file:helpers.Excel.ExcelDataFormat.java
public Object unmarshal(Exchange exchng, InputStream in) throws Exception { logger.info("Unmarshalling XLS"); Object res = exchng.getIn().getBody(); GenericFile genfile = (GenericFile) res; if (genfile.getFileNameOnly().endsWith("xlsx")) { return unmarshalXLSX(exchng, in); }/* www . ja v a 2s .co m*/ HSSFWorkbook workbook = new HSSFWorkbook(in); FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); //Get first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0); if (importType != ImportType.FORMATTED) { return marshalAsArray(sheet.iterator()); } else { OneExcel excel = new OneExcel(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { OneExcelSheet onesheet = marshalAsStructure(workbook.getSheetAt(i).iterator(), evaluator); logger.info("Loading sheet:" + i); logger.info("Data:" + onesheet.data.size()); if (onesheet.data.size() > 0) excel.sheets.add(onesheet); } logger.info("Total sheets:" + excel.sheets.size()); ArrayList<HashMap<String, Object>> resu = excel.GenerateResult(); HashMap<String, Object> mappings = excel.GenerateMappings(); exchng.getOut().setHeader("mappings", mappings); exchng.getOut().setHeader("xlsdata", resu); return resu; } }
From source file:in.igsa.upload.FileUploadAction.java
License:Apache License
public String upload() throws Exception { try {/* w w w . ja va 2s . c o m*/ Map<String, Object> session = ActionContext.getContext().getSession(); List<String> regionNames = new ArrayList<String>(); List<String> modelNames = new ArrayList<String>(); List<String> unitNames = new ArrayList<String>(); List<String> variableNames = new ArrayList<String>(); String emailError = ""; regionNames = service.getRegionNames(); modelNames = service.getModelNames(); unitNames = service.getUnitNames(); variableNames = service.getVariableNames(); String dateTime; Date date = new Date(); dateTime = DateTime.getDateTime1(date); String filePath = servletRequest.getSession().getServletContext().getRealPath("/") + "/files/"; File theFile; parent = new ArrayList<FileUploadVo>(); rowHeader = new FileUploadVo(); System.out.println(" contentType : " + contentType); boolean row_header = true; if ("application/vnd.ms-excel".equalsIgnoreCase(contentType)) { try { theFile = new File(filePath, DateTime.getFileForUpload(date) + ".xls"); if (theFile.exists()) { System.out.println(" file esist "); } else { System.out.println(" null file"); } fileNameForUpload = DateTime.getFileForUpload(date) + ".xls"; FileUtils.copyFile(upload, theFile); FileInputStream file = new FileInputStream(theFile); //new File("\\files\\howtodoinjava_demo.xls")); //Create Workbook instance holding reference to .xls file HSSFWorkbook workbook = new HSSFWorkbook(file); //Get first/desired sheet from the workbook (.xls) HSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { HSSFRow row = (HSSFRow) rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); child = new FileUploadVo(); yearVal = new ArrayList<String>(); year = new ArrayList<String>(); int i = 1; if (row_header) { while (cellIterator.hasNext()) { HSSFCell cell = (HSSFCell) cellIterator.next(); //Check the cell type and format accordingly if (i > 5) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: mapHeader.put(i + "", cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: mapHeader.put(i + "", cell.getStringCellValue()); break; } } i++; } row_header = false; } else { while (cellIterator.hasNext()) { HSSFCell cell = (HSSFCell) cellIterator.next(); //Check the cell type and format accordingly if (i == 1) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setModel(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setModel(cell.getStringCellValue()); break; } } else if (i == 2) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setScenario(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setScenario(cell.getStringCellValue()); break; } } else if (i == 3) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setRegion(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setRegion(cell.getStringCellValue()); break; } } else if (i == 4) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setVariable(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setVariable(cell.getStringCellValue()); break; } } else if (i == 5) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setUnit(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setUnit(cell.getStringCellValue()); break; } } else { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: yearVal.add(cell.getNumericCellValue() + ""); year.add(mapHeader.get(i + "").toString()); break; case Cell.CELL_TYPE_STRING: yearVal.add(cell.getStringCellValue() + ""); year.add(mapHeader.get(i + "").toString()); break; } } //parent.add(child); //child.setValue(value); i++; } child.setVal(yearVal); child.setYear(year); child.setDateTime(dateTime); child.setUploadedBy(session.get("user_id").toString()); child.setFilePath("files/" + fileNameForUpload); parent.add(child); } } file.close(); } catch (Exception e) { e.printStackTrace(); } service.deleteModelSceRegion(child); for (FileUploadVo aa : parent) { boolean flag = true; if (!modelNames.contains(aa.getModel())) { emailError = emailError + "ERROR : Model name <i>" + aa.getModel() + "</i> not in list of valid Model name. <br>"; flag = false; } if (!regionNames.contains(aa.getRegion())) { emailError = emailError + "ERROR : Region name <i>" + aa.getRegion() + "</i> not in list of valid Region name. <br>"; flag = false; } if (!variableNames.contains(aa.getVariable())) { emailError = emailError + "ERROR : Variable name <i>" + aa.getVariable() + "</i> not in list of valid Variable name. <br>"; flag = false; } if (!unitNames.contains(aa.getUnit())) { emailError = emailError + "ERROR : Unit name <i>" + aa.getUnit() + "</i> not in list of valid Unit name. <br>"; flag = false; } if (flag) service.insertFileUpload(aa); else emailError = emailError + "********************************************<br>"; } } else if ("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet".equals(contentType)) { try { theFile = new File(filePath, DateTime.getFileForUpload(date) + ".xlsx"); fileNameForUpload = DateTime.getFileForUpload(date) + ".xlsx"; FileUtils.copyFile(upload, theFile); FileInputStream file = new FileInputStream(theFile); //new File("\\files\\howtodoinjava_demo.xls")); //Create Workbook instance holding reference to .xlsx file XSSFWorkbook workbook = new XSSFWorkbook(file); //Get first/desired sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); //Iterate through each rows one by one Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { XSSFRow row = (XSSFRow) rowIterator.next(); //For each row, iterate through all the columns Iterator<Cell> cellIterator = row.cellIterator(); child = new FileUploadVo(); yearVal = new ArrayList<String>(); year = new ArrayList<String>(); int i = 1; if (row_header) { while (cellIterator.hasNext()) { XSSFCell cell = (XSSFCell) cellIterator.next(); //Check the cell type and format accordingly if (i > 5) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: mapHeader.put(i + "", cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: mapHeader.put(i + "", cell.getStringCellValue()); break; } } i++; } row_header = false; } else { while (cellIterator.hasNext()) { XSSFCell cell = (XSSFCell) cellIterator.next(); //Check the cell type and format accordingly if (i == 1) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setModel(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setModel(cell.getStringCellValue()); break; } } else if (i == 2) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setScenario(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setScenario(cell.getStringCellValue()); break; } } else if (i == 3) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setRegion(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setRegion(cell.getStringCellValue()); break; } } else if (i == 4) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setVariable(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setVariable(cell.getStringCellValue()); break; } } else if (i == 5) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: child.setUnit(cell.getNumericCellValue() + ""); break; case Cell.CELL_TYPE_STRING: child.setUnit(cell.getStringCellValue()); break; } } else { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: yearVal.add(cell.getNumericCellValue() + ""); year.add(mapHeader.get(i + "").toString()); break; case Cell.CELL_TYPE_STRING: yearVal.add(cell.getStringCellValue() + ""); year.add(mapHeader.get(i + "").toString()); break; } } //parent.add(child); //child.setValue(value); i++; } child.setVal(yearVal); child.setYear(year); child.setDateTime(dateTime); child.setUploadedBy(session.get("user_id").toString()); child.setFilePath("files/" + fileNameForUpload); parent.add(child); System.out.println("\n"); } } file.close(); } catch (Exception e) { e.printStackTrace(); } service.deleteModelSceRegion(child); for (FileUploadVo aa : parent) { boolean flag = true; if (!modelNames.contains(aa.getModel())) { emailError = emailError + "ERROR : Model name " + aa.getModel() + " not in list of valid Model name. <br>"; flag = false; } if (!regionNames.contains(aa.getRegion())) { emailError = emailError + "ERROR : Region name " + aa.getRegion() + " not in list of valid Region name. <br>"; flag = false; } if (!variableNames.contains(aa.getVariable())) { emailError = emailError + "ERROR : Variable name " + aa.getVariable() + " not in list of valid Variable name. <br>"; flag = false; } if (!unitNames.contains(aa.getUnit())) { emailError = emailError + "ERROR : Unit name " + aa.getUnit() + " not in list of valid Unit name. <br>"; flag = false; } if (flag) service.insertFileUpload(aa); else emailError = emailError + "********************************************<br>"; } } else { addActionError(" File Format Should be xls or xlsx "); scenarioReport = service.getScenarioReport(); return INPUT; } String subject = "Error : undefined parameters"; String content = "Dear " + session.get("user_id").toString() + " <br> <br> here's a brief report about your scenarios data upload to the SSP database." + "Please do open and carefully check the attached log file to find out whether the import was successful.<br><br>Regards,<br>SSP database admin Summary <br><br><br>"; if (emailError != "") SendEmail.send("4igsalabs@gmail.com", session.get("user_email").toString(), subject, content + emailError); } catch (Exception e) { addActionError(e.getCause().getLocalizedMessage()); scenarioReport = service.getScenarioReport(); linkAction(scenarioReport); return INPUT; } scenarioReport = service.getScenarioReport(); linkAction(scenarioReport); return SUCCESS; }
From source file:it.cineca.pst.huborcid.service.AbstractFileService.java
License:Open Source License
@Transactional(propagation = Propagation.REQUIRES_NEW) public void uploadFileOrcid(MultipartFile file, Application application, String typeEntity) throws IOException { log.debug(String.format("Method uploadFileOrcid START, file.name=[%s]", file.getOriginalFilename())); Integer maxColumn = 0;//from w w w . j a v a2s.com ResultUploadOrcidEntity resultUploadOrcid = new ResultUploadOrcidEntity(); try { resultUploadOrcid.setApplication(application); resultUploadOrcid.setFileNameUpload(file.getOriginalFilename()); resultUploadOrcid.setStatus("PROGRESS"); resultUploadOrcid.setEntityType(typeEntity); resultUploadOrcidEntityRepository.save(resultUploadOrcid); OrcidAccessToken orcidAccessToken = new OrcidAccessToken(); InputStream fileInputStream = new BufferedInputStream(file.getInputStream()); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet sheet = workbook.getSheetAt(0); boolean withErrors = false; Iterator<Row> rowIterator = sheet.iterator(); OrcidOAuthClient clientOrcid = new OrcidOAuthClient(orcidApiType); while (rowIterator.hasNext()) { Row row = rowIterator.next(); if (row.getRowNum() == 0) { maxColumn = row.getPhysicalNumberOfCells(); } else { try { String valueCellLocalId = ""; String valueCellOrcid = ""; Cell cell = row.getCell(0); if (cell != null) { valueCellLocalId = cell.getStringCellValue(); } cell = row.getCell(1); if (cell != null) { valueCellOrcid = cell.getStringCellValue(); } List<RelPersonApplication> listPersApp = relPersonApplicationRepository .findAllByApplicationIsAndLastIsTrueAndOrcidIsOrLocalIdIs(application, valueCellOrcid, valueCellLocalId); if (listPersApp.size() == 1) { RelPersonApplication persApp = listPersApp.get(0); orcidAccessToken.setAccess_token(persApp.getOauthAccessToken()); orcidAccessToken.setOrcid(persApp.getPerson().getOrcid()); createAppendEntity(clientOrcid, orcidAccessToken, sheet, row); writeResultRow(row, maxColumn, "", true); } else if (listPersApp.size() == 0) { writeResultRow(row, maxColumn, "Utente non trovato", false); withErrors = true; } else if (listPersApp.size() > 1) { writeResultRow(row, maxColumn, "Errore di sistema: anagrafiche duplicate", false); withErrors = true; } } catch (Exception e) { writeResultRow(row, maxColumn, e.getMessage(), false); withErrors = true; } } } ByteArrayOutputStream baos = new ByteArrayOutputStream(); workbook.write(baos); workbook.close(); byte[] fileResult = baos.toByteArray(); resultUploadOrcid.setStatus("COMPLETED"); resultUploadOrcid.setWithErrors(withErrors); resultUploadOrcid.setFileResult(fileResult); resultUploadOrcidEntityRepository.save(resultUploadOrcid); } catch (Exception e) { log.debug(String.format("Method uploadFileOrcid, exception=[%s]", e.getMessage())); resultUploadOrcid.setStatus("ERROR"); resultUploadOrcidEntityRepository.save(resultUploadOrcid); } log.debug("Method uploadFileOrcid END"); }
From source file:jschsftp.JSCHsftp.java
public static void main(String[] args) throws Exception { JSCHsftp mJSCHsftp = new JSCHsftp(); mJSCHsftp.createConnection();/*from ww w. jav a 2 s . c om*/ FileInputStream fin = new FileInputStream(strXlsPath); HSSFWorkbook myWorkBook = new HSSFWorkbook(fin); HSSFSheet mySheet = myWorkBook.getSheetAt(0); Iterator<Row> rowIterator = mySheet.iterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); String strFolderName = getCellValue(row.getCell(FOLDER_NAME_INDEX)); System.out.println("Home Directory >> " + mJSCHsftp.mChannelSftp.getHome()); System.out.println("Present Directory >> " + mJSCHsftp.mChannelSftp.pwd()); if (mJSCHsftp.mChannelSftp != null) mJSCHsftp.startTransfer(mJSCHsftp.mChannelSftp, "./" + strFolderName, strLocalPath); } mJSCHsftp.closeConnections(); }
From source file:LoadExcels.loadPMTCT_FO.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {//from w w w . ja v a 2 s . co m year = quarter = mflcode = Numerator = Denominator = checker = missing = added = updated = 0; String linked_art = "0"; String not_linked_art = "0"; String unknown_link = "0"; String not_breastfeeding = "0"; String breastfeeding = "0"; String breastfeeding_unknown = "0"; String care_no_test = "0"; String ltfu = "0"; String died = "0"; String transferred_out = "0"; session = request.getSession(); dbConn conn = new dbConn(); nextpage = "loadExcel.jsp"; String applicationPath = request.getServletContext().getRealPath(""); String uploadFilePath = applicationPath + File.separator + UPLOAD_DIR; session = request.getSession(); File fileSaveDir = new File(uploadFilePath); if (!fileSaveDir.exists()) { fileSaveDir.mkdirs(); } System.out.println("Upload File Directory=" + fileSaveDir.getAbsolutePath()); for (Part part : request.getParts()) { fileName = getFileName(part); part.write(uploadFilePath + File.separator + fileName); System.out.println("file name is : " + fileName); } if (!fileName.endsWith(".xls")) { nextpage = "loadExcel.jsp"; session.setAttribute("upload_success", "<font color=\"red\">Failed to load the excel file. Please choose the correct File.</font>"); } else { full_path = fileSaveDir.getAbsolutePath() + "\\" + fileName; System.out.println("the saved file directory is : " + full_path); // GET DATA FROM THE EXCEL AND AND OUTPUT IT ON THE CONSOLE.................................. FileInputStream fileInputStream = new FileInputStream(full_path); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet("PMTCT-FO"); Iterator rowIterator = worksheet.iterator(); int i = 1, y = 0; while (rowIterator.hasNext()) { HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "loadExcel.jsp"; break; } //Year Quarter County Sub County Health Facility MFL Code Type of support Numerator Denominator HIV-infected:Linked to ART HIV-infected: Not linked to ART HIV-infected : Unknown link HIV-uninfected:Not beastfeeding HIV-uninfected: Still breastfeeeding HIV-uninfected:Breastfeeding unknown Other outcomes: In care but not test done Other outcomes:Lost to follow up Other outcomes : Died Other outcomes:Transferred out int rowcount = 0; HSSFCell cellYear = rowi.getCell((short) rowcount); rowcount++; year = (int) cellYear.getNumericCellValue(); HSSFCell cellQuarter = rowi.getCell((short) rowcount); rowcount = rowcount + 3; quarterName = cellQuarter.getStringCellValue(); HSSFCell cellFacilityName = rowi.getCell((short) rowcount); facilityName = cellFacilityName.getStringCellValue(); rowcount++; //HSSFCell cellMFLCODE = rowi.getCell((short) rowcount); //mflcode = Integer.parseInt(cellMFLCODE.getStringCellValue()); if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount = rowcount + 2; if (cellrow.getCellType() == 0) { //numeric mflcode = (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { mflcode = new Integer(cellrow.getStringCellValue()); } } HSSFCell cellNumerator = rowi.getCell((short) rowcount); rowcount++; Numerator = (int) cellNumerator.getNumericCellValue(); HSSFCell cellDenominator = rowi.getCell((short) rowcount); rowcount++; Denominator = (int) cellDenominator.getNumericCellValue(); //linked_art if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric linked_art = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { linked_art = cellrow.getStringCellValue(); } } //not_linked_art if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric not_linked_art = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { not_linked_art = cellrow.getStringCellValue(); } } //unknown_link if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric unknown_link = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { unknown_link = cellrow.getStringCellValue(); } } // //not_breastfeeding if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric not_breastfeeding = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { not_breastfeeding = cellrow.getStringCellValue(); } } //breastfeeding if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric breastfeeding = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { breastfeeding = cellrow.getStringCellValue(); } } //breastfeeding_unknown if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric breastfeeding_unknown = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { breastfeeding_unknown = cellrow.getStringCellValue(); } } //care_no_test if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric care_no_test = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { care_no_test = cellrow.getStringCellValue(); } } //ltfu if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric ltfu = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { ltfu = cellrow.getStringCellValue(); } } //died if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric died = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { died = cellrow.getStringCellValue(); } } //transferred_out if (1 == 1) { HSSFCell cellrow = rowi.getCell((short) rowcount); rowcount++; if (cellrow.getCellType() == 0) { //numeric transferred_out = "" + (int) cellrow.getNumericCellValue(); } else if (cellrow.getCellType() == 1) { transferred_out = cellrow.getStringCellValue(); } } facilityID = ""; checker = 0; String get_id = "SELECT SubPartnerID FROM subpartnera WHERE CentreSanteId=?"; conn.pst = conn.conn.prepareStatement(get_id); conn.pst.setInt(1, mflcode); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { facilityID = conn.rs.getString(1); } if (facilityID.length() > 0) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String getQuarterID = "SELECT id FROM quarter WHERE pmtct_fo_name=?"; conn.pst = conn.conn.prepareStatement(getQuarterID); conn.pst.setString(1, quarterName); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { quarter = conn.rs.getInt(1); } checker = 0; // CHECK IF ALREADY ADDED TO PMTCT_FO TABLE id = year + "_" + quarter + "_" + facilityID; // System.out.println("to add data : "+facilityName+" id : "+facilityID+"mfl code "+mflcode+" year : "+year+" quarter : "+quarter+" numerator : "+Numerator+" denominator : "+Denominator); String checkerExisting = "SELECT id FROM pmtct_fo WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } // // // //,,,,,,,,,, if (checker == 0) { String inserter = "INSERT INTO pmtct_fo (id,SubPartnerID,year,quarter,numerator,denominator,linked_art,not_linked_art,unknown_link,not_breastfeeding,breastfeeding,breastfeeding_unknown,care_no_test,ltfu,died,transferred_out) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, id); conn.pst.setString(2, facilityID); conn.pst.setInt(3, year); conn.pst.setInt(4, quarter); conn.pst.setInt(5, Numerator); conn.pst.setInt(6, Denominator); conn.pst.setString(7, linked_art); conn.pst.setString(8, not_linked_art); conn.pst.setString(9, unknown_link); conn.pst.setString(10, not_breastfeeding); conn.pst.setString(11, breastfeeding); conn.pst.setString(12, breastfeeding_unknown); conn.pst.setString(13, care_no_test); conn.pst.setString(14, ltfu); conn.pst.setString(15, died); conn.pst.setString(16, transferred_out); conn.pst.executeUpdate(); added++; } else { String inserter = "UPDATE pmtct_fo SET SubPartnerID=?,year=?,quarter=?,numerator=?,denominator=? ,linked_art=? ,not_linked_art = ? ,unknown_link = ? ,not_breastfeeding = ? ,breastfeeding = ? ,breastfeeding_unknown = ? ,care_no_test = ? ,ltfu = ? ,died = ? , transferred_out = ? WHERE id=?"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, facilityID); conn.pst.setInt(2, year); conn.pst.setInt(3, quarter); conn.pst.setInt(4, Numerator); conn.pst.setInt(5, Denominator); conn.pst.setString(6, linked_art); conn.pst.setString(7, not_linked_art); conn.pst.setString(8, unknown_link); conn.pst.setString(9, not_breastfeeding); conn.pst.setString(10, breastfeeding); conn.pst.setString(11, breastfeeding_unknown); conn.pst.setString(12, care_no_test); conn.pst.setString(13, ltfu); conn.pst.setString(14, died); conn.pst.setString(15, transferred_out); conn.pst.setString(16, id); conn.pst.executeUpdate(); updated++; } } else { missing++; // missing facilities missingFacility += "facility name : " + facilityName + " mfl code : " + mflcode + " excel row num : " + i + "<br>"; System.out.println(facilityName + "facility is missing mflcode :" + mflcode); } i++; } //a code to loop through all synced records without a last month //the affected tables are "eid_datim","viral_load","pmtct_fo","tb_stat_art" AddLastMonth am = new AddLastMonth(); am.addfirstmonth(); //end of sync last month } } catch (SQLException ex) { Logger.getLogger(loadPMTCT_FO.class.getName()).log(Level.SEVERE, null, ex); } String sessionText = "Data for <b>" + added + "</b> sites newly added. <br/> Data for <b>" + updated + "</b> updated. <br/>Data for <b>" + missing + "</b> sites skipped because they are missing in IMIS"; session.setAttribute("pmtctresponse", sessionText); response.sendRedirect(nextpage); }
From source file:LoadExcels.loadPMTCT_FO_OLD.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {//from w ww .j a v a 2s. com session = request.getSession(); dbConn conn = new dbConn(); nextpage = "loadExcel.jsp"; String applicationPath = request.getServletContext().getRealPath(""); String uploadFilePath = applicationPath + File.separator + UPLOAD_DIR; session = request.getSession(); File fileSaveDir = new File(uploadFilePath); if (!fileSaveDir.exists()) { fileSaveDir.mkdirs(); } System.out.println("Upload File Directory=" + fileSaveDir.getAbsolutePath()); for (Part part : request.getParts()) { fileName = getFileName(part); part.write(uploadFilePath + File.separator + fileName); System.out.println("file name is : " + fileName); } if (!fileName.endsWith(".xls")) { nextpage = "loadExcel.jsp"; session.setAttribute("upload_success", "<font color=\"red\">Failed to load the excel file. Please choose the correct File.</font>"); } else { full_path = fileSaveDir.getAbsolutePath() + "\\" + fileName; System.out.println("the saved file directory is : " + full_path); // GET DATA FROM THE EXCEL AND AND OUTPUT IT ON THE CONSOLE.................................. FileInputStream fileInputStream = new FileInputStream(full_path); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheet("PMTCT-FO"); Iterator rowIterator = worksheet.iterator(); int i = 2, y = 0; while (rowIterator.hasNext()) { HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "loadExcel.jsp"; break; } HSSFCell cellYear = rowi.getCell((short) 0); year = (int) cellYear.getNumericCellValue(); HSSFCell cellQuarter = rowi.getCell((short) 1); quarterName = cellQuarter.getStringCellValue(); HSSFCell cellFacilityName = rowi.getCell((short) 4); facilityName = cellFacilityName.getStringCellValue(); HSSFCell cellMFLCODE = rowi.getCell((short) 5); mflcode = Integer.parseInt(cellMFLCODE.getStringCellValue()); HSSFCell cellNumerator = rowi.getCell((short) 7); Numerator = (int) cellNumerator.getNumericCellValue(); HSSFCell cellDenominator = rowi.getCell((short) 8); Denominator = (int) cellDenominator.getNumericCellValue(); //linked_art //not_linked_art //unknown_link //not_breastfeeding //breastfeeding //breastfeeding_unknown //care_no_test //ltfu //died //transferred_out facilityID = ""; checker = 0; String get_id = "SELECT SubPartnerID FROM subpartnera WHERE CentreSanteId=?"; conn.pst = conn.conn.prepareStatement(get_id); conn.pst.setInt(1, mflcode); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { facilityID = conn.rs.getString(1); } if (facilityID.length() > 0) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String getQuarterID = "SELECT id FROM quarter WHERE pmtct_fo_name=?"; conn.pst = conn.conn.prepareStatement(getQuarterID); conn.pst.setString(1, quarterName); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { quarter = conn.rs.getInt(1); } checker = 0; // CHECK IF ALREADY ADDED TO PMTCT_FO TABLE id = year + "_" + quarter + "_" + facilityID; // System.out.println("to add data : "+facilityName+" id : "+facilityID+"mfl code "+mflcode+" year : "+year+" quarter : "+quarter+" numerator : "+Numerator+" denominator : "+Denominator); String checkerExisting = "SELECT id FROM pmtct_fo WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } // // // if (checker == 0) { String inserter = "INSERT INTO pmtct_fo (id,SubPartnerID,year,quarter,numerator,denominator) " + "VALUES(?,?,?,?,?,?)"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, id); conn.pst.setString(2, facilityID); conn.pst.setInt(3, year); conn.pst.setInt(4, quarter); conn.pst.setInt(5, Numerator); conn.pst.setInt(6, Denominator); conn.pst.executeUpdate(); added++; } else { String inserter = "UPDATE pmtct_fo SET SubPartnerID=?,year=?,quarter=?,numerator=?,denominator=? WHERE id=?"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, facilityID); conn.pst.setInt(2, year); conn.pst.setInt(3, quarter); conn.pst.setInt(4, Numerator); conn.pst.setInt(5, Denominator); conn.pst.setString(6, id); conn.pst.executeUpdate(); updated++; } } else { missing++; // missing facilities missingFacility += "facility name : " + facilityName + " mfl code : " + mflcode + " excel row num : " + i + "<br>"; System.out.println(facilityName + "facility is missing mflcode :" + mflcode); } i++; } //a code to loop through all synced records without a last month //the affected tables are "eid_datim","viral_load","pmtct_fo","tb_stat_art" AddLastMonth am = new AddLastMonth(); am.addfirstmonth(); //end of sync last month } } catch (SQLException ex) { Logger.getLogger(loadPMTCT_FO.class.getName()).log(Level.SEVERE, null, ex); } String sessionText = added + "New data added <> " + updated + " updated facilities<br> and " + missing + " missing facilities"; response.sendRedirect(nextpage); }
From source file:LoadExcels.loadTBExcel.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {/*www . jav a2s.c o m*/ session = request.getSession(); dbConn conn = new dbConn(); nextpage = "loadTBExcel.jsp"; String female_stat, male_stat; String less1, stat_1to4, stat_5to9, stat_10to14, stat_15to19, stat_20above, positive, negative; String art_numerator, art_denominator, art_female; String art_male, art_less1, art_1to4, art_5to9, art_10to14, art_15to19, art_20above; female_stat = male_stat = ""; less1 = stat_1to4 = stat_5to9 = stat_10to14 = stat_15to19 = stat_20above = positive = negative = ""; art_numerator = art_denominator = art_female = ""; art_male = art_less1 = art_1to4 = art_5to9 = art_10to14 = art_15to19 = art_20above = ""; String applicationPath = request.getServletContext().getRealPath(""); String uploadFilePath = applicationPath + File.separator + UPLOAD_DIR; session = request.getSession(); File fileSaveDir = new File(uploadFilePath); if (!fileSaveDir.exists()) { fileSaveDir.mkdirs(); } System.out.println("Upload File Directory=" + fileSaveDir.getAbsolutePath()); for (Part part : request.getParts()) { fileName = getFileName(part); part.write(uploadFilePath + File.separator + fileName); System.out.println("file name is : " + fileName); } if (!fileName.endsWith(".xls")) { nextpage = "loadExcel.jsp"; session.setAttribute("upload_success", "<font color=\"red\">Failed to load the excel file. Please choose the correct File.</font>"); } else { full_path = fileSaveDir.getAbsolutePath() + "\\" + fileName; System.out.println("the saved file directory is : " + full_path); // GET DATA FROM THE EXCEL AND AND OUTPUT IT ON THE CONSOLE.................................. FileInputStream fileInputStream = new FileInputStream(full_path); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheetAt(0); Iterator rowIterator = worksheet.iterator(); int i = 2, y = 0; while (rowIterator.hasNext()) { System.out.println(" in while " + i); HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "loadTBExcel.jsp"; break; } HSSFCell cellYear = rowi.getCell((short) 0); year = (int) cellYear.getNumericCellValue(); HSSFCell cellQuarter = rowi.getCell((short) 1); quarterName = cellQuarter.getStringCellValue(); HSSFCell cellFacilityName = rowi.getCell((short) 4); facilityName = cellFacilityName.getStringCellValue(); HSSFCell cellMFLCODE = rowi.getCell((short) 5); if (cellMFLCODE.getCellType() == 0) { //numeric mflcode = "" + (int) cellMFLCODE.getNumericCellValue(); } else if (cellMFLCODE.getCellType() == 1) { mflcode = cellMFLCODE.getStringCellValue(); } HSSFCell cellstype = rowi.getCell((short) 6); String supporttype = cellstype.getStringCellValue(); HSSFCell cellNumerator = rowi.getCell((short) 7); Numerator = "" + (int) cellNumerator.getNumericCellValue(); HSSFCell cellDenominator = rowi.getCell((short) 8); Denominator = (int) cellDenominator.getNumericCellValue(); HSSFCell cellfemale = rowi.getCell((short) 9); female_stat = "" + (int) cellfemale.getNumericCellValue(); HSSFCell cellmale = rowi.getCell((short) 10); male_stat = "" + (int) cellmale.getNumericCellValue(); HSSFCell cellless1 = rowi.getCell((short) 11); less1 = "" + (int) cellless1.getNumericCellValue(); HSSFCell cell1to4 = rowi.getCell((short) 12); stat_1to4 = "" + (int) cell1to4.getNumericCellValue(); HSSFCell cell5to9 = rowi.getCell((short) 13); stat_5to9 = "" + (int) cell5to9.getNumericCellValue(); HSSFCell cell10to14 = rowi.getCell((short) 14); stat_10to14 = "" + (int) cell10to14.getNumericCellValue(); HSSFCell cell15to19 = rowi.getCell((short) 15); stat_15to19 = "" + (int) cell15to19.getNumericCellValue(); HSSFCell cell20above = rowi.getCell((short) 16); stat_20above = "" + (int) cell20above.getNumericCellValue(); HSSFCell cellpositive = rowi.getCell((short) 17); positive = "" + (int) cellpositive.getNumericCellValue(); HSSFCell cellnegative = rowi.getCell((short) 18); negative = "" + (int) cellnegative.getNumericCellValue(); HSSFCell cellart_numerator = rowi.getCell((short) 19); art_numerator = "" + (int) cellart_numerator.getNumericCellValue(); HSSFCell cellart_denominator = rowi.getCell((short) 20); art_denominator = "" + (int) cellart_denominator.getNumericCellValue(); HSSFCell cellart_female = rowi.getCell((short) 21); art_female = "" + (int) cellart_female.getNumericCellValue(); HSSFCell cellart_male = rowi.getCell((short) 22); art_male = "" + (int) cellart_male.getNumericCellValue(); HSSFCell cellart_less1 = rowi.getCell((short) 23); art_less1 = "" + (int) cellart_less1.getNumericCellValue(); HSSFCell cellart_1to4 = rowi.getCell((short) 24); art_1to4 = "" + (int) cellart_1to4.getNumericCellValue(); HSSFCell cellart_5to9 = rowi.getCell((short) 25); art_5to9 = "" + (int) cellart_5to9.getNumericCellValue(); HSSFCell cellart_10to14 = rowi.getCell((short) 26); art_10to14 = "" + (int) cellart_10to14.getNumericCellValue(); HSSFCell cellart_15to19 = rowi.getCell((short) 27); art_15to19 = "" + (int) cellart_15to19.getNumericCellValue(); HSSFCell cellart_20above = rowi.getCell((short) 28); art_20above = "" + (int) cellart_20above.getNumericCellValue(); // // int female_stat,male_stat; //int less1, stat_1to4,stat_5to9,stat_10to14,stat_15to19,stat_20above,positive,negative; // //int art_numerator, art_denominator, art_female; //int art_male,art_less1,art_1to4,art_5to9,art_10to14,art_15to19,art_20above; facilityID = ""; checker = 0; String get_id = "SELECT SubPartnerID FROM subpartnera WHERE CentreSanteId=?"; conn.pst = conn.conn.prepareStatement(get_id); conn.pst.setString(1, mflcode); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { facilityID = conn.rs.getString(1); } if (facilityID.length() > 0) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String getQuarterID = "SELECT id FROM quarter WHERE pmtct_fo_name=?"; conn.pst = conn.conn.prepareStatement(getQuarterID); conn.pst.setString(1, quarterName); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { quarter = conn.rs.getInt(1); } checker = 0; // CHECK IF ALREADY ADDED TO PMTCT_FO TABLE id = year + "_" + quarter + "_" + facilityID; // System.out.println("to add data : "+facilityName+" id : "+facilityID+"mfl code "+mflcode+" year : "+year+" quarter : "+quarter+" numerator : "+Numerator+" denominator : "+Denominator); String checkerExisting = "SELECT id FROM tb_stat_art WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } // // // if (checker == 0) { String inserter = "INSERT INTO tb_stat_art (id,SubPartnerID,year,quarter,numerator,denominator,female,male,less1,1to4,5to9,10to14,15to19,20above,positive,negative,art_numerator,art_denominator,art_female," + "art_male,art_less1,art_1to4,art_5to9,art_10to14,art_15to19,art_20above,supporttype) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, id); conn.pst.setString(2, facilityID); conn.pst.setInt(3, year); conn.pst.setInt(4, quarter); conn.pst.setString(5, Numerator); conn.pst.setInt(6, Denominator); conn.pst.setString(7, female_stat); conn.pst.setString(8, male_stat); conn.pst.setString(9, less1); conn.pst.setString(10, stat_1to4); conn.pst.setString(11, stat_5to9); conn.pst.setString(12, stat_10to14); conn.pst.setString(13, stat_15to19); conn.pst.setString(14, stat_20above); conn.pst.setString(15, positive); conn.pst.setString(16, negative); conn.pst.setString(17, art_numerator); conn.pst.setString(18, art_denominator); conn.pst.setString(19, art_female); conn.pst.setString(20, art_male); conn.pst.setString(21, art_less1); conn.pst.setString(22, art_1to4); conn.pst.setString(23, art_5to9); conn.pst.setString(24, art_10to14); conn.pst.setString(25, art_15to19); conn.pst.setString(26, art_20above); conn.pst.setString(27, supporttype); conn.pst.executeUpdate(); added++; } else { String inserter = "UPDATE tb_stat_art SET SubPartnerID=?,year=?,quarter=?,numerator=?,denominator=?, " + "female=?,male=?,less1=?,1to4=?,5to9=?,10to14=?,15to19=?,20above=?,positive=?,negative=?,art_numerator=?,art_denominator=?," + "art_female=?,art_male=?,art_less1=?,art_1to4=?,art_5to9=?,art_10to14=?,art_15to19=?,art_20above=?,supporttype=?" + "WHERE id=?"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, facilityID); conn.pst.setInt(2, year); conn.pst.setInt(3, quarter); conn.pst.setString(4, Numerator); conn.pst.setInt(5, Denominator); conn.pst.setString(6, female_stat); conn.pst.setString(7, male_stat); conn.pst.setString(8, less1); conn.pst.setString(9, stat_1to4); conn.pst.setString(10, stat_5to9); conn.pst.setString(11, stat_10to14); conn.pst.setString(12, stat_15to19); conn.pst.setString(13, stat_20above); conn.pst.setString(14, positive); conn.pst.setString(15, negative); conn.pst.setString(16, art_numerator); conn.pst.setString(17, art_denominator); conn.pst.setString(18, art_female); conn.pst.setString(19, art_male); conn.pst.setString(20, art_less1); conn.pst.setString(21, art_1to4); conn.pst.setString(22, art_5to9); conn.pst.setString(23, art_10to14); conn.pst.setString(24, art_15to19); conn.pst.setString(25, art_20above); conn.pst.setString(26, supporttype); conn.pst.setString(27, id); conn.pst.executeUpdate(); updated++; } } else { missing++; // missing facilities missingFacility += "facility name : " + facilityName + " mfl code : " + mflcode + " excel row num : " + i + "<br>"; System.out.println(facilityName + "facility is missing mflcode :" + mflcode); } i++; } } } catch (SQLException ex) { Logger.getLogger(loadTBExcel.class.getName()).log(Level.SEVERE, null, ex); } String sessionText = added + "New data added <> " + updated + " updated facilities<br> and " + missing + " missing facilities"; session.setAttribute("upload_success", sessionText); response.sendRedirect(nextpage); }
From source file:LoadExcels.loadViralLoad.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try {//from w w w . j av a 2 s . c o m session = request.getSession(); dbConn conn = new dbConn(); nextpage = "loadTBExcel.jsp"; //--------------------------------------------------------------------- String numerator_un, denominator_un; String fun_less1, fun_1to4, fun_5to14, fun_15to19, fun_20; String mun_less1, mun_1to4, mun_5to14, mun_15to19, mun_20; String subtotal_un, numerator_vi, denominator_vi; String fvi_less1, fvi_1to4, fvi_5to14, fvi_15to19, fvi_20; String mvi_less1, mvi_1to4, mvi_5to14, mvi_15to19, mvi_20, subtotal_vi; //--------------------------------------------------------------------- numerator_un = denominator_un = ""; fun_less1 = fun_1to4 = fun_5to14 = fun_15to19 = fun_20 = ""; mun_less1 = mun_1to4 = mun_5to14 = mun_15to19 = mun_20 = ""; subtotal_un = numerator_vi = denominator_vi = ""; fvi_less1 = fvi_1to4 = fvi_5to14 = fvi_15to19 = fvi_20 = ""; mvi_less1 = mvi_1to4 = mvi_5to14 = mvi_15to19 = mvi_20 = subtotal_vi = ""; String applicationPath = request.getServletContext().getRealPath(""); String uploadFilePath = applicationPath + File.separator + UPLOAD_DIR; session = request.getSession(); File fileSaveDir = new File(uploadFilePath); if (!fileSaveDir.exists()) { fileSaveDir.mkdirs(); } System.out.println("Upload File Directory=" + fileSaveDir.getAbsolutePath()); for (Part part : request.getParts()) { fileName = getFileName(part); part.write(uploadFilePath + File.separator + fileName); System.out.println("file name is : " + fileName); } if (!fileName.endsWith(".xls")) { nextpage = "loadExcel.jsp"; session.setAttribute("upload_success", "<font color=\"red\">Failed to load the excel file. Please choose the correct File.</font>"); } else { full_path = fileSaveDir.getAbsolutePath() + "\\" + fileName; System.out.println("the saved file directory is : " + full_path); // GET DATA FROM THE EXCEL AND AND OUTPUT IT ON THE CONSOLE.................................. FileInputStream fileInputStream = new FileInputStream(full_path); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheetAt(0); Iterator rowIterator = worksheet.iterator(); int i = 2, y = 0; while (rowIterator.hasNext()) { System.out.println(" in while"); HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "loadViralLoad.jsp"; break; } HSSFCell cellYear = rowi.getCell((short) 0); year = (int) cellYear.getNumericCellValue(); HSSFCell cellQuarter = rowi.getCell((short) 1); quarterName = cellQuarter.getStringCellValue(); HSSFCell cellFacilityName = rowi.getCell((short) 4); facilityName = cellFacilityName.getStringCellValue(); HSSFCell cellMFLCODE = rowi.getCell((short) 5); mflcode = cellMFLCODE.getStringCellValue(); HSSFCell cellstype = rowi.getCell((short) 6); String supporttype = cellstype.getStringCellValue(); HSSFCell cellNumerator = rowi.getCell((short) 7); numerator_un = "" + (int) cellNumerator.getNumericCellValue(); HSSFCell cellDenominator = rowi.getCell((short) 8); denominator_un = "" + (int) cellDenominator.getNumericCellValue(); HSSFCell less1funcell = rowi.getCell((short) 9); fun_less1 = "" + (int) less1funcell.getNumericCellValue(); HSSFCell cl10 = rowi.getCell((short) 10); fun_1to4 = "" + (int) cl10.getNumericCellValue(); HSSFCell cl11 = rowi.getCell((short) 11); fun_5to14 = "" + (int) cl11.getNumericCellValue(); HSSFCell cl12 = rowi.getCell((short) 12); fun_15to19 = "" + (int) cl12.getNumericCellValue(); HSSFCell cl13 = rowi.getCell((short) 13); fun_20 = "" + (int) cl13.getNumericCellValue(); HSSFCell cl14 = rowi.getCell((short) 14); mun_less1 = "" + (int) cl14.getNumericCellValue(); HSSFCell cl15 = rowi.getCell((short) 15); mun_1to4 = "" + (int) cl15.getNumericCellValue(); HSSFCell cl16 = rowi.getCell((short) 16); mun_5to14 = "" + (int) cl16.getNumericCellValue(); HSSFCell cl17 = rowi.getCell((short) 17); mun_15to19 = "" + (int) cl17.getNumericCellValue(); HSSFCell cl18 = rowi.getCell((short) 18); mun_20 = "" + (int) cl18.getNumericCellValue(); HSSFCell cl19 = rowi.getCell((short) 19); subtotal_un = "" + (int) cl19.getNumericCellValue(); HSSFCell cl20 = rowi.getCell((short) 20); numerator_vi = "" + (int) cl20.getNumericCellValue(); HSSFCell cl21 = rowi.getCell((short) 21); denominator_vi = "" + (int) cl21.getNumericCellValue(); HSSFCell cl22 = rowi.getCell((short) 22); fvi_less1 = "" + (int) cl22.getNumericCellValue(); HSSFCell cl23 = rowi.getCell((short) 23); fvi_1to4 = "" + (int) cl23.getNumericCellValue(); HSSFCell cl24 = rowi.getCell((short) 24); fvi_5to14 = "" + (int) cl24.getNumericCellValue(); HSSFCell cl25 = rowi.getCell((short) 25); fvi_15to19 = "" + (int) cl25.getNumericCellValue(); HSSFCell cl26 = rowi.getCell((short) 26); fvi_20 = "" + (int) cl26.getNumericCellValue(); HSSFCell cl27 = rowi.getCell((short) 27); mvi_less1 = "" + (int) cl27.getNumericCellValue(); HSSFCell cl27a = rowi.getCell((short) 28); mvi_1to4 = "" + (int) cl27a.getNumericCellValue(); HSSFCell cl28 = rowi.getCell((short) 29); mvi_5to14 = "" + (int) cl28.getNumericCellValue(); HSSFCell cl29 = rowi.getCell((short) 30); mvi_15to19 = "" + (int) cl29.getNumericCellValue(); // HSSFCell cl30 = rowi.getCell((short) 31); mvi_20 = "" + (int) cl30.getNumericCellValue(); HSSFCell cl31 = rowi.getCell((short) 32); subtotal_vi = "" + (int) cl31.getNumericCellValue(); // int female_stat,male_stat; //int less1, stat_1to4,stat_5to9,stat_10to14,stat_15to19,stat_20above,positive,negative; // //int art_numerator, art_denominator, art_female; //int art_male,art_less1,art_1to4,art_5to9,art_10to14,art_15to19,art_20above; facilityID = ""; checker = 0; String get_id = "SELECT SubPartnerID FROM subpartnera WHERE CentreSanteId=?"; conn.pst = conn.conn.prepareStatement(get_id); conn.pst.setString(1, mflcode); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { facilityID = conn.rs.getString(1); } if (facilityID.length() > 0) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String getQuarterID = "SELECT id FROM quarter WHERE pmtct_fo_name=?"; conn.pst = conn.conn.prepareStatement(getQuarterID); conn.pst.setString(1, quarterName); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { quarter = conn.rs.getInt(1); } checker = 0; // CHECK IF ALREADY ADDED TO PMTCT_FO TABLE id = year + "_" + quarter + "_" + facilityID; // System.out.println("to add data : "+facilityName+" id : "+facilityID+"mfl code "+mflcode+" year : "+year+" quarter : "+quarter+" numerator : "+Numerator+" denominator : "+Denominator); String checkerExisting = "SELECT id FROM viral_load WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } // // // if (checker == 0) { System.out.println("INSERT >> " + numerator_un); String inserter = "INSERT INTO viral_load (id,SubPartnerID,year,quarter,numerator_un ,denominator_un,less1_fun,1to4_fun,5to14_fun,15to19_fun,20_fun,less1_mun,1to4_mun,5to14_mun,15to19_mun,20_mun,subtotal_un,numerator_vi,denominator_vi,less1_fvi,1to4_fvi ,5to14_fvi,15to19_fvi,20_fvi,less1_mvi,1to4_mvi,5to14_mvi,15to19_mvi,20_mvi ,subtotal_vi,supporttype) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, id); conn.pst.setString(2, facilityID); conn.pst.setInt(3, year); conn.pst.setInt(4, quarter); conn.pst.setString(5, numerator_un); conn.pst.setString(6, denominator_un); conn.pst.setString(7, fun_less1); conn.pst.setString(8, fun_1to4); conn.pst.setString(9, fun_5to14); conn.pst.setString(10, fun_15to19); conn.pst.setString(11, fun_20); conn.pst.setString(12, mun_less1); conn.pst.setString(13, mun_1to4); conn.pst.setString(14, mun_5to14); conn.pst.setString(15, mun_15to19); conn.pst.setString(16, mun_20); conn.pst.setString(17, subtotal_un); conn.pst.setString(18, numerator_vi); conn.pst.setString(19, denominator_vi); conn.pst.setString(20, fvi_less1); conn.pst.setString(21, fvi_1to4); conn.pst.setString(22, fvi_5to14); conn.pst.setString(23, fvi_15to19); conn.pst.setString(24, fvi_20); conn.pst.setString(25, mvi_less1); conn.pst.setString(26, mvi_1to4); conn.pst.setString(27, mvi_5to14); conn.pst.setString(28, mvi_15to19); conn.pst.setString(29, mvi_20); conn.pst.setString(30, subtotal_vi); conn.pst.setString(31, supporttype); conn.pst.executeUpdate(); added++; } else { String inserter = "UPDATE viral_load SET SubPartnerID=?,year=?,quarter=?,numerator_un =?,denominator_un=?,less1_fun=?,1to4_fun=?,5to14_fun=?,15to19_fun=?,20_fun=?,less1_mun=?,1to4_mun=?,5to14_mun=?,15to19_mun=?,20_mun=?,subtotal_un=?,numerator_vi=?,denominator_vi=?,less1_fvi=?,1to4_fvi=? ,5to14_fvi=?,15to19_fvi=?,20_fvi=?,less1_mvi=?,1to4_mvi=?,5to14_mvi=?,15to19_mvi=?,20_mvi=?,subtotal_vi=?,supporttype=?" + "WHERE id=?"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, facilityID); conn.pst.setInt(2, year); conn.pst.setInt(3, quarter); conn.pst.setString(4, numerator_un); conn.pst.setString(5, denominator_un); conn.pst.setString(6, fun_less1); conn.pst.setString(7, fun_1to4); conn.pst.setString(8, fun_5to14); conn.pst.setString(9, fun_15to19); conn.pst.setString(10, fun_20); conn.pst.setString(11, mun_less1); conn.pst.setString(12, mun_1to4); conn.pst.setString(13, mun_5to14); conn.pst.setString(14, mun_15to19); conn.pst.setString(15, mun_20); conn.pst.setString(16, subtotal_un); conn.pst.setString(17, numerator_vi); conn.pst.setString(18, denominator_vi); conn.pst.setString(19, fvi_less1); conn.pst.setString(20, fvi_1to4); conn.pst.setString(21, fvi_5to14); conn.pst.setString(22, fvi_15to19); conn.pst.setString(23, fvi_20); conn.pst.setString(24, mvi_less1); conn.pst.setString(25, mvi_1to4); conn.pst.setString(26, mvi_5to14); conn.pst.setString(27, mvi_15to19); conn.pst.setString(28, mvi_20); conn.pst.setString(29, subtotal_vi); conn.pst.setString(30, supporttype); conn.pst.setString(31, id); conn.pst.executeUpdate(); updated++; } } else { missing++; // missing facilities missingFacility += "facility name : " + facilityName + " mfl code : " + mflcode + " excel row num : " + i + "<br>"; System.out.println(facilityName + "facility is missing mflcode :" + mflcode); } i++; } } } catch (SQLException ex) { Logger.getLogger(loadTBExcel.class.getName()).log(Level.SEVERE, null, ex); } String sessionText = added + "New data added <> " + updated + " updated facilities<br> and " + missing + " missing facilities"; response.sendRedirect(nextpage); }
From source file:LoadExcels.Load_tb_raw.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String serialnumber = ""; String distregno = ""; String agebracket = ""; String registrationdate = ""; String registrationdatecopy = ""; int age = 0;/* ww w . j a v a2 s .c om*/ String agestring = ""; String sex = "F"; String treatmentstartdate = ""; String hivtestdate = ""; String hivstatus = ""; String artstatus = ""; String artdate = ""; String treatmentoutcome = ""; String treatmentoutcomedate = ""; int ageinteger = 0; String supporttype = ""; String datacategory = ""; String dbname = "tibu_tb_raw"; String tbtype = ""; String patienttype = ""; String smear0 = ""; String smear2_3 = ""; String smear5 = ""; String smear6_8 = ""; String genexpert = ""; // if(request.getParameter("datacategory")!=null){ // datacategory=request.getParameter("datacategory"); // System.out.println(" data category fetched"); // } if (datacategory.equals("completeoutcome")) { //this is the data for monthly uploads dbname = "tibu_tb_raw_outcome"; } try { session = request.getSession(); dbConn conn = new dbConn(); nextpage = "sync_tb.jsp"; //--------------------------------------------------------------------- String applicationPath = request.getServletContext().getRealPath(""); String uploadFilePath = applicationPath + File.separator + UPLOAD_DIR; session = request.getSession(); File fileSaveDir = new File(uploadFilePath); if (!fileSaveDir.exists()) { fileSaveDir.mkdirs(); } System.out.println("Upload File Directory=" + fileSaveDir.getAbsolutePath()); for (Part part : request.getParts()) { fileName = getFileName(part); part.write(uploadFilePath + File.separator + fileName); System.out.println("file name is : " + fileName); } if (!fileName.endsWith(".xls")) { nextpage = "upload_tb_raw_data.jsp"; session.setAttribute("upload_success", "<font color=\"red\">Failed to load the excel file. Please choose the correct File.</font>"); } else { full_path = fileSaveDir.getAbsolutePath() + "\\" + fileName; System.out.println("the saved file directory is : " + full_path); // GET DATA FROM THE EXCEL AND AND OUTPUT IT ON THE CONSOLE.................................. FileInputStream fileInputStream = new FileInputStream(full_path); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheetAt(0); Iterator rowIterator = worksheet.iterator(); int i = 1, y = 0; while (rowIterator.hasNext()) { // System.out.println(" in while"); HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "sync_tb.jsp"; break; } //([0])Serial Number ([1])Date of Registration ([2])District Registration Number (3)Province (4)County (5)District (6)Zone ([7])Health Facility ([8])Year ([9])Quarter (10)Sector (11)Patient Name ([12])Sex M/F ([13])Age on registration (14)Weight (Kgs) (15)Height (Mtrs) (16)BMI (17)MUAC (18)Physical address (Neighbor,Primary School) Cell Phone (19)DOT by (20)Type of TB P/EP (21)EPTB Sub Type (22)EPTB Others (23)Type of patient (24)CD4 First Date (25)CD4 Last Date (26)Culture S (27)Culture R (28)Culture E (29)Culture H (30)X-ray Y/N (31)Sputum Smear Examination (32)0th Month Result (33)0th Month Serial No and Quantification (34)Sputum Smear Examination 2by3 Month Result (35)2by3 Month Serial No and Quantification (36)Sputum Smear Examination 5th Month Result (37)5th Month Serial No and Quantification (38)Sputum Smear Examination 6by8 Month Result (39)6by8 Month Serial No and Quantification (40)Regimen ([41])Date of treatment started (42)Gen expert (43)Lipa Hain Rifampicin (44)Lipa Hain Isoniazid ([45])HIV Test Date ([46])HIV Status (47)Partner HIV Test Date (48)Partner HIV Status (49)Referred BY: VCT/HCC/STI/HBC/PS/ANC/SR/CI (50)Referred TO VCT/HCC/STI/HBC/PS/ANC (51)Cotrimoxazole Preventive Therapy Y/N (52)Cotrimoxazole Preventive Therapy (Date Started) ([53])ART Y/N ([54])ART (Date Started) (55)Nutrition Support ([56])Treatment Outcome ([57])Treatment Outcome Date ([58])Remarks //<tr><td>(0)Serial Number</td><td>(1)Date of Registration</td><td>(2)District Registration Number</td><td>(3)Province</td><td>(4) County</td><td>(5)District</td><td>(6)Zone</td><td>(7)Health Facility</td><td>(8)Year</td><td>(9)Quarter</td><td>(10)Sector</td><td>(11)Patient Name</td><td>(12)Sex M/F</td><td>(13)Age on registration</td><td>(14)Weight (Kgs)</td><td>(15)Height (Mtrs)</td><td>(16)BMI</td><td>(17)MUAC</td><td>(18)Physical address (Neighbor,Primary School) Cell Phone</td><td>(19)DOT by</td><td>(20)Type of TB P/EP</td><td>(21)EPTB Sub Type</td><td>(22) EPTB Others</td><td>(23)Type of patient </td><td>(24)CD4 First Date</td><td>(25)CD4 Last Date</td><td>(26)Culture S</td><td>(27)Culture R</td><td>(28)Culture E </td><td>(29)Culture H</td><td>(30)X-ray Y/N</td><td>(31)Sputum Smear Examination</td><td>(32) 0th Month Result</td><td>(33)0th Month Serial No and Quantification</td><td>(34)Sputum Smear Examination 2by3 Month Result</td><td>(35)2by3 Month Serial No and Quantification</td><td>(36)Sputum Smear Examination 5th Month Result</td><td>(37)5th Month Serial No and Quantification</td><td>(38)Sputum Smear Examination 6by8 Month Result</td><td>(39)6by8 Month Serial No and Quantification</td><td>(40)Regimen</td><td>(41)Date of treatment started</td><td>(42)Gen expert</td><td>(43)Lipa Hain Rifampicin</td><td>(44)Lipa Hain Isoniazid</td><td>(45)HIV Test Date</td><td>(46)HIV Status</td><td>(47)Partner HIV Test Date</td><td>(48)Partner HIV Status</td><td>(49)Referred BY: VCT/HCC/STI/HBC/PS/ANC/SR/CI</td><td>(50)Referred TO VCT/HCC/STI/HBC/PS/ANC</td><td>(51)Cotrimoxazole Preventive Therapy Y/N</td><td>(52)Cotrimoxazole Preventive Therapy (Date Started)</td><td>(53)ART Y/N</td><td>(54)ART (Date Started)</td><td>(55)Nutrition Support</td><td>(56)Treatment Outcome</td><td>(57)Treatment Outcome Date</td><td>(58)Remarks</td></tr> //______________________________________________________________________ //([0])Serial Number //([1])Date of Registration //([2])District Registration Number //([4])County //([5])District //([7])Health Facility //([8])Year //([9])Quarter //([12])Sex M/F //([13])Age on registration //([40])Date of treatment started //([44])HIV Test Date //([45])HIV Status //([52])ART Y/N //([53])ART (Date Started) //([55])Treatment Outcome //([56])Treatment Outcome Date //([20])Type of TB P/EP //([23])Type of patient //([31])Sputum Smear Examination 0th Month Result //([33])Sputum Smear Examination 2by3 Month Result //([35])Sputum Smear Examination 5th Month Result //([37])Sputum Smear Examination 6by8 Month Result //([41])Gen expert //tbtype //patienttype //smear0 //smear2_3 //smear5 //smear6_8 //genexpert //______________________________________________________________________ HSSFCell cellserialno = rowi.getCell((short) 0); if (cellserialno.getCellType() == 0) { //numeric serialnumber = "" + (int) cellserialno.getNumericCellValue(); } else if (cellserialno.getCellType() == 1) { serialnumber = cellserialno.getStringCellValue(); } { } HSSFCell cellregdate = rowi.getCell((short) 1); // System.out.println("CELLTYPE IS "+cellregdate.getCellType()); if (cellregdate.getCellType() == 1) { //this is a string registrationdate = (String) cellregdate.getStringCellValue(); } else if (cellregdate.getCellType() == 0) { //this is a numeric value registrationdate = "" + (int) cellregdate.getNumericCellValue(); } else { registrationdate = "" + cellregdate.getDateCellValue(); } // System.out.println("Reg Date "+registrationdate); //District Reg number HSSFCell celldistreg = rowi.getCell((short) 2); if (celldistreg.getCellType() == 0) { //numeric distregno = "" + (int) celldistreg.getNumericCellValue(); } else if (celldistreg.getCellType() == 1) { distregno = celldistreg.getStringCellValue(); } { } //dont save county and subcounty directly since they may change HSSFCell cellcounty = rowi.getCell((short) 4); county_name = cellcounty.getStringCellValue(); HSSFCell cellsubcounty = rowi.getCell((short) 5); district_name = cellsubcounty.getStringCellValue(); HSSFCell cellfacil = rowi.getCell((short) 7); facilityName = cellfacil.getStringCellValue(); //HSSFCell cellmfl = rowi.getCell((short) 7); //mflcode = (int) cellmfl.getNumericCellValue(); HSSFCell cellsex = rowi.getCell((short) 12); if (cellsex.getCellType() == 1) { //string sex = (String) cellsex.getStringCellValue(); } else { //numeric sex = "" + (int) cellsex.getNumericCellValue(); } HSSFCell agestringcell = rowi.getCell((short) 13); if (agestringcell.getCellType() == 1) { //string agestring = (String) agestringcell.getStringCellValue(); } else { //numeric agestring = "" + (int) agestringcell.getNumericCellValue(); } if (agestring.contains("M") && agestring.contains("Y")) { //age is in months String agearray[] = agestring.split(" "); //System.out.println("agestring "+agestring+" _ "+age); age = ((new Double(agearray[0].trim().replace("Y", ""))).intValue()) + (int) Math.round((new Double(agearray[1].replace("M", "")) / 12)); System.out.println(" agestring M&Y " + agestring + " _ " + age); } else if (agestring.contains("Y") && !agestring.contains("M")) { //age is in years age = (new Double(agestring.trim().replace("Y", ""))).intValue(); System.out.println(" agestring Y Only " + agestring + " _ " + age); } else if (agestring.contains("M") && !agestring.contains("Y")) { //age is in months //System.out.println("agestring "+agestring+" _ "+age); age = (int) Math.round((new Double(agestring.trim().replace("M", "")) / 12)); // System.out.println("agestring "+agestring+" _ "+age); System.out.println(" agestring M Only " + agestring + " _ " + age); } ageinteger = age; agebracket = getageBracket(ageinteger); //tbtype HSSFCell celltbtype = rowi.getCell((short) 20); if (celltbtype.getCellType() == 1) { //this is a string tbtype = (String) celltbtype.getStringCellValue(); } else if (celltbtype.getCellType() == 0) { //this is a numeric value tbtype = "" + (int) celltbtype.getNumericCellValue(); } else { tbtype = "" + celltbtype.getDateCellValue(); } //type of patient HSSFCell cellpatienttype = rowi.getCell((short) 23); if (cellpatienttype.getCellType() == 1) { //this is a string patienttype = (String) cellpatienttype.getStringCellValue(); } else if (cellpatienttype.getCellType() == 0) { //this is a numeric value patienttype = "" + (int) cellpatienttype.getNumericCellValue(); } else { patienttype = "" + cellpatienttype.getDateCellValue(); } //------------ //smear0 HSSFCell cellsmear0 = rowi.getCell((short) 31); if (cellsmear0.getCellType() == 1) { //this is a string smear0 = (String) cellsmear0.getStringCellValue(); } else if (cellsmear0.getCellType() == 0) { //this is a numeric value smear0 = "" + (int) cellsmear0.getNumericCellValue(); } else { smear0 = "" + cellsmear0.getDateCellValue(); } //------------ HSSFCell cellsmear2_3 = rowi.getCell((short) 33); if (cellsmear2_3.getCellType() == 1) { //this is a string smear2_3 = (String) cellsmear0.getStringCellValue(); } else if (cellsmear2_3.getCellType() == 0) { //this is a numeric value smear2_3 = "" + (int) cellsmear2_3.getNumericCellValue(); } else { smear2_3 = "" + cellsmear0.getDateCellValue(); } //------------smear5------ HSSFCell cellsmear5 = rowi.getCell((short) 35); if (cellsmear5.getCellType() == 1) { //this is a string smear5 = (String) cellsmear0.getStringCellValue(); } else if (cellsmear5.getCellType() == 0) { //this is a numeric value smear5 = "" + (int) cellsmear5.getNumericCellValue(); } else { smear5 = "" + cellsmear5.getDateCellValue(); } //------------ //------------smear5------ HSSFCell cellsmear6_8 = rowi.getCell((short) 37); if (cellsmear6_8.getCellType() == 1) { //this is a string smear6_8 = (String) cellsmear0.getStringCellValue(); } else if (cellsmear6_8.getCellType() == 0) { //this is a numeric value smear6_8 = "" + (int) cellsmear6_8.getNumericCellValue(); } else { smear6_8 = "" + cellsmear6_8.getDateCellValue(); } //------------------- HSSFCell celltreatmentdate = rowi.getCell((short) 40); if (cellregdate.getCellType() == 1) { //this is a string treatmentstartdate = (String) celltreatmentdate.getStringCellValue(); } else if (cellregdate.getCellType() == 0) { //this is a numeric value treatmentstartdate = "" + (int) celltreatmentdate.getNumericCellValue(); } else { treatmentstartdate = "" + celltreatmentdate.getDateCellValue(); } //------------smear5------ HSSFCell cellgenexpert = rowi.getCell((short) 41); if (cellgenexpert.getCellType() == 1) { //this is a string genexpert = (String) cellgenexpert.getStringCellValue(); } else if (cellgenexpert.getCellType() == 0) { //this is a numeric value genexpert = "" + (int) cellgenexpert.getNumericCellValue(); } else { genexpert = "" + cellgenexpert.getDateCellValue(); } //------------------- //treatmentstartdate=""+celltreatmentdate.getDateCellValue(); //Format formatter = new SimpleDateFormat("MM/dd/yyyy"); //registrationdatecopy= new SimpleDateFormat("MM/dd/yyyy").format(registrationdate); // registrationdatecopy = formatter.format(registrationdatecopy); //hiv test date HSSFCell cellhivtestdate = rowi.getCell((short) 44); hivtestdate = "" + cellhivtestdate.getStringCellValue(); //hiv status HSSFCell cellhivstatus = rowi.getCell((short) 45); hivstatus = (String) cellhivstatus.getStringCellValue(); //art status HSSFCell cellartstatus = rowi.getCell((short) 52); artstatus = (String) cellartstatus.getStringCellValue(); //art date HSSFCell cellartdate = rowi.getCell((short) 53); artdate = "" + cellartdate.getStringCellValue(); //treatment outcome HSSFCell celltreatmentoutcome = rowi.getCell((short) 55); treatmentoutcome = (String) celltreatmentoutcome.getStringCellValue(); //treatment outcome date HSSFCell celltreatmentoutcomedate = rowi.getCell((short) 56); treatmentoutcomedate = "" + celltreatmentoutcomedate.getStringCellValue(); //split the date, year and month //raw date is of form dd mmm yyyy eg 08 Jul 2015 String dateparameters[] = registrationdate.split(" "); if (dateparameters.length == 3) { if (!dateparameters[0].equals("")) { String month = ""; month = dateparameters[1]; if (month.equalsIgnoreCase("Jan") || month.equalsIgnoreCase("Feb") || month.equalsIgnoreCase("Mar")) { quarterName = "January-March"; if (dateparameters[2].length() == 4) { year = Integer.parseInt(dateparameters[2]); } } else if (month.equalsIgnoreCase("Apr") || month.equalsIgnoreCase("May") || month.equalsIgnoreCase("Jun")) { quarterName = "April-June"; if (dateparameters[2].length() == 4) { year = Integer.parseInt(dateparameters[2]); } } else if (month.equalsIgnoreCase("Jul") || month.equalsIgnoreCase("Aug") || month.equalsIgnoreCase("Sep")) { quarterName = "July-September"; if (dateparameters[2].length() == 4) { year = Integer.parseInt(dateparameters[2]); } } else if (month.equalsIgnoreCase("Oct") || month.equalsIgnoreCase("Nov") || month.equalsIgnoreCase("Dec")) { quarterName = "October-December"; if (dateparameters[2].length() == 4) { //assume year = Integer.parseInt(dateparameters[2]) + 1; } } } } else { System.out.println("Error in date of testing _ :" + registrationdate); } // System.out.println("Quarter "+quarterName + "Year "+year); facilityID = ""; checker = 0; supporttype = "DSD"; String get_id = "SELECT SubPartnerID,ART_Support,CentreSanteId as mflcode,HTC_Support1,PMTCT_Support FROM subpartnera WHERE SubPartnerNom like ? or tibu_name like ?"; conn.pst = conn.conn.prepareStatement(get_id); conn.pst.setString(1, "%" + facilityName + "%"); conn.pst.setString(2, "%" + facilityName + "%"); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { facilityID = conn.rs.getString(1); //supporttype=conn.rs.getString("ART_Support"); mflcode = conn.rs.getInt(3); //if(supporttype==null){supporttype=conn.rs.getString("HTC_Support1");} //if(supporttype==null){supporttype=conn.rs.getString("PMTCT_Support");} //if(supporttype==null){supporttype="";} } if (facilityID.length() > 0) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String getQuarterID = "SELECT id FROM quarter WHERE pmtct_fo_name like ?"; conn.pst = conn.conn.prepareStatement(getQuarterID); conn.pst.setString(1, quarterName); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { quarter = conn.rs.getInt(1); } checker = 0; // CHECK IF ALREADY ADDED TO PMTCT_FO TABLE id = serialnumber + "_" + registrationdate + "_" + distregno; // System.out.println("to add data : "+facilityName+" id : "+facilityID+"mfl code "+mflcode+" year : "+year+" quarter : "+quarter+" numerator : "+Numerator+" denominator : "+Denominator); String checkerExisting = "SELECT id FROM " + dbname + " WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } //id //SubPartnerID //Quarter //Year //Sex //Mflcode //age //agebracket //SubPartnerNom //dateoftesting //patientccc //batchno //suppression_status if (checker == 0) { String inserter = "INSERT INTO " + dbname + " (id,SubPartnerID,year,quarter,Mflcode,sex ,age,agebracket,SubPartnerNom,registrationdate,treatmentdate,supporttype,hivstatus,hivtestdate, " + " artstatus,artdate,outcomedate,treatmentoutcome,tbtype,patienttype,smear0,smear2_3,smear5,smear6_8,genexpert) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, id); conn.pst.setString(2, facilityID); conn.pst.setInt(3, year); conn.pst.setInt(4, quarter); conn.pst.setInt(5, mflcode); conn.pst.setString(6, sex); conn.pst.setInt(7, age); conn.pst.setString(8, agebracket); conn.pst.setString(9, facilityName); conn.pst.setString(10, registrationdate); conn.pst.setString(11, treatmentstartdate); conn.pst.setString(12, supporttype); conn.pst.setString(13, hivstatus); conn.pst.setString(14, hivtestdate); conn.pst.setString(15, artstatus); conn.pst.setString(16, artdate); conn.pst.setString(17, treatmentoutcomedate); conn.pst.setString(18, treatmentoutcome); conn.pst.setString(19, tbtype); conn.pst.setString(20, patienttype); conn.pst.setString(21, smear0); conn.pst.setString(22, smear2_3); conn.pst.setString(23, smear5); conn.pst.setString(24, smear6_8); conn.pst.setString(25, genexpert); conn.pst.executeUpdate(); added++; } else { //id,SubPartnerID,Year,Quarter,Mflcode,Sex ,age,agebracket,SubPartnerNom,dateoftesting,patientccc,batchno,supporttype String inserter = " UPDATE " + dbname + " SET SubPartnerID=?,year=?,quarter=?,Mflcode=?,sex=? ,age=?,agebracket=?,SubPartnerNom=?,registrationdate=?,treatmentdate=?,supporttype=?," + " hivstatus=?,hivtestdate=?, " + " artstatus=?,artdate=?,outcomedate=?,treatmentoutcome=? ,tbtype=?,patienttype=?,smear0=?,smear2_3=?,smear5=?,smear6_8=?,genexpert=?" + " WHERE id=?"; // conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, facilityID); conn.pst.setInt(2, year); conn.pst.setInt(3, quarter); conn.pst.setInt(4, mflcode); conn.pst.setString(5, sex); conn.pst.setInt(6, age); conn.pst.setString(7, agebracket); conn.pst.setString(8, facilityName); conn.pst.setString(9, registrationdate); conn.pst.setString(10, treatmentoutcomedate); conn.pst.setString(11, supporttype); conn.pst.setString(12, hivstatus); conn.pst.setString(13, hivtestdate); conn.pst.setString(14, artstatus); conn.pst.setString(15, artdate); conn.pst.setString(16, treatmentoutcomedate); conn.pst.setString(17, treatmentoutcome); conn.pst.setString(18, tbtype); conn.pst.setString(19, patienttype); conn.pst.setString(20, smear0); conn.pst.setString(21, smear2_3); conn.pst.setString(22, smear5); conn.pst.setString(23, smear6_8); conn.pst.setString(24, genexpert); conn.pst.setString(25, id); conn.pst.executeUpdate(); updated++; } } else { missing++; // missing facilities missingFacility += "facility name : " + facilityName + " mfl code : " + mflcode + " excel row num : " + i + "<br>"; System.out.println(facilityName + "_missing"); } i++; } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.pst != null) { conn.pst.close(); } } catch (SQLException ex) { Logger.getLogger(Load_tb_raw.class.getName()).log(Level.SEVERE, null, ex); } String sessionText = "<br/><b> " + added + "</b> New data added <br/> <b> " + updated + "</b> updated facilities<br> <br> <b>" + missing + "</b> sites not in Imis Facilities List "; session.setAttribute("upload_success", sessionText); response.sendRedirect(nextpage); }
From source file:LoadExcels.Load_viral_load_raw.java
@Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { String patientccc = ""; String suppression = ""; String testingdate = ""; String agebracket = ""; String dateoftesting = ""; int age = 0;// w w w .j av a2s . com String sex = ""; String batchno = ""; String supporttype = ""; int ageinteger = 0; try { session = request.getSession(); dbConn conn = new dbConn(); nextpage = "sync_viral_load.jsp"; //--------------------------------------------------------------------- String numerator_un, denominator_un; String fun_less1, fun_1to4, fun_5to14, fun_15to19, fun_20; String mun_less1, mun_1to4, mun_5to14, mun_15to19, mun_20; String subtotal_un, numerator_vi, denominator_vi; String fvi_less1, fvi_1to4, fvi_5to14, fvi_15to19, fvi_20; String mvi_less1, mvi_1to4, mvi_5to14, mvi_15to19, mvi_20, subtotal_vi; //--------------------------------------------------------------------- numerator_un = denominator_un = ""; fun_less1 = fun_1to4 = fun_5to14 = fun_15to19 = fun_20 = ""; mun_less1 = mun_1to4 = mun_5to14 = mun_15to19 = mun_20 = ""; subtotal_un = numerator_vi = denominator_vi = ""; fvi_less1 = fvi_1to4 = fvi_5to14 = fvi_15to19 = fvi_20 = ""; mvi_less1 = mvi_1to4 = mvi_5to14 = mvi_15to19 = mvi_20 = subtotal_vi = ""; String applicationPath = request.getServletContext().getRealPath(""); String uploadFilePath = applicationPath + File.separator + UPLOAD_DIR; session = request.getSession(); File fileSaveDir = new File(uploadFilePath); if (!fileSaveDir.exists()) { fileSaveDir.mkdirs(); } System.out.println("Upload File Directory=" + fileSaveDir.getAbsolutePath()); for (Part part : request.getParts()) { fileName = getFileName(part); part.write(uploadFilePath + File.separator + fileName); System.out.println("file name is : " + fileName); } if (!fileName.endsWith(".xls")) { nextpage = "sync_viral_load.jsp"; session.setAttribute("upload_success", "<font color=\"red\">Failed to load the excel file. Please choose the correct File.</font>"); } else { full_path = fileSaveDir.getAbsolutePath() + "\\" + fileName; System.out.println("the saved file directory is : " + full_path); // GET DATA FROM THE EXCEL AND AND OUTPUT IT ON THE CONSOLE.................................. FileInputStream fileInputStream = new FileInputStream(full_path); HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheetAt(0); Iterator rowIterator = worksheet.iterator(); int i = 2, y = 0; while (rowIterator.hasNext()) { System.out.println(" in while"); HSSFRow rowi = worksheet.getRow(i); if (rowi == null) { nextpage = "sync_viral_load.jsp"; break; } //#(0) Batch No(1) Patient CCC No(2) Testing Lab(3) County (4) District(5) Facility Name(6) MFL Code(7) Sex(8) Age(9) Sample Type(10) Collection Date(11) Received Status(12) Reason for Repeat(13) Regimen (14) Justification (15) ART Initiation Date(16) Date of Receiving(17) Date of Testing(18) Date of Dispatch (19) Result(cp/ml) (20) Result(Log) (21) Suppressed? (22) //Patient CCC No(2) County (4) District(5) Facility Name(6) MFL Code(7) Sex(8) Age(9) Date of Testing(18) Suppressed? (22) HSSFCell cellbatcno = rowi.getCell((short) 1); if (cellbatcno.getCellType() == 0) { //numeric batchno = "" + (int) cellbatcno.getNumericCellValue(); } else if (cellbatcno.getCellType() == 1) { batchno = cellbatcno.getStringCellValue(); } { } HSSFCell cellpatienceno = rowi.getCell((short) 2); System.out.println("CELLTYPE IS " + cellpatienceno.getCellType()); if (cellpatienceno.getCellType() == 1) { //this is a string patientccc = (String) cellpatienceno.getStringCellValue(); } else if (cellpatienceno.getCellType() == 0) { //this is a numeric value patientccc = "" + (int) cellpatienceno.getNumericCellValue(); } else { patientccc = (String) cellpatienceno.getStringCellValue(); } //dont save county and subcounty directly since they may change HSSFCell cellcounty = rowi.getCell((short) 4); county_name = cellcounty.getStringCellValue(); HSSFCell cellsubcounty = rowi.getCell((short) 5); district_name = cellsubcounty.getStringCellValue(); HSSFCell cellfacil = rowi.getCell((short) 6); facilityName = cellfacil.getStringCellValue(); HSSFCell cellmfl = rowi.getCell((short) 7); if (cellmfl.getCellType() == 1) { //string mflcode = new Integer(cellmfl.getStringCellValue()); } else { //numeric mflcode = (int) cellmfl.getNumericCellValue(); } HSSFCell cellsex = rowi.getCell((short) 8); if (cellsex.getCellType() == 1) { //string sex = (String) cellsex.getStringCellValue(); } else { //numeric sex = "" + (int) cellsex.getNumericCellValue(); } HSSFCell cellage = rowi.getCell((short) 9); age = (int) cellage.getNumericCellValue(); ageinteger = age; agebracket = getageBracket(ageinteger); HSSFCell celldate = rowi.getCell((short) 18); dateoftesting = "" + celldate.getStringCellValue(); //Format formatter = new SimpleDateFormat("MM/dd/yyyy"); // dateoftesting= new SimpleDateFormat("MM/dd/yyyy").format(celldate.getDateCellValue()); //dateoftesting = formatter.format(dateoftesting); HSSFCell cellsuppression = rowi.getCell((short) 22); if (cellsuppression.getCellType() == 1) { //string suppression = cellsuppression.getStringCellValue(); } else { suppression = "" + (int) cellsuppression.getNumericCellValue(); } //split the date, year and month //raw date is of form m/d/yyyy String dateparameters[] = dateoftesting.split("-"); if (dateparameters.length == 3) { if (!dateparameters[0].equals("")) { String month = ""; month = dateparameters[1]; if (month.equals("01") || month.equals("02") || month.equals("03")) { quarterName = "January-March"; if (dateparameters[0].length() == 4) { year = Integer.parseInt(dateparameters[0]); } } else if (month.equals("04") || month.equals("05") || month.equals("06")) { quarterName = "April-June"; if (dateparameters[0].length() == 4) { year = Integer.parseInt(dateparameters[0]); } } else if (month.equals("07") || month.equals("08") || month.equals("09")) { quarterName = "July-September"; if (dateparameters[0].length() == 4) { year = Integer.parseInt(dateparameters[0]); } } else if (month.equals("10") || month.equals("11") || month.equals("12")) { quarterName = "October-December"; if (dateparameters[0].length() == 4) { //assume year = Integer.parseInt(dateparameters[0]) + 1; } } } } else { System.out.println("Error in date of testing _ :" + dateoftesting); } System.out.println("Quarter " + quarterName + "Year " + year); facilityID = ""; checker = 0; String get_id = "SELECT SubPartnerID,ART_Support FROM subpartnera WHERE CentreSanteId=?"; conn.pst = conn.conn.prepareStatement(get_id); conn.pst.setInt(1, mflcode); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { facilityID = conn.rs.getString(1); supporttype = conn.rs.getString(2); if (supporttype == null) { supporttype = ""; } } if (facilityID.length() > 0 && !sex.equals("")) { // DISTRICT FOUND ADD THE HF TO THE SYSTEM......................... String getQuarterID = "SELECT id FROM quarter WHERE pmtct_fo_name like ?"; conn.pst = conn.conn.prepareStatement(getQuarterID); conn.pst.setString(1, quarterName); conn.rs = conn.pst.executeQuery(); if (conn.rs.next() == true) { quarter = conn.rs.getInt(1); } checker = 0; // CHECK IF ALREADY ADDED TO PMTCT_FO TABLE id = batchno + "_" + patientccc + "_" + dateoftesting; // System.out.println("to add data : "+facilityName+" id : "+facilityID+"mfl code "+mflcode+" year : "+year+" quarter : "+quarter+" numerator : "+Numerator+" denominator : "+Denominator); String checkerExisting = "SELECT id FROM viral_load_raw WHERE id='" + id + "'"; conn.rs = conn.st.executeQuery(checkerExisting); if (conn.rs.next() == true) { checker++; } //id //SubPartnerID //Quarter //Year //Sex //Mflcode //age //agebracket //SubPartnerNom //dateoftesting //patientccc //batchno //suppression_status if (checker == 0) { System.out.println("INSERT >> " + numerator_un); String inserter = "INSERT INTO viral_load_raw (id,SubPartnerID,Year,Quarter,Mflcode,Sex ,age,agebracket,SubPartnerNom,dateoftesting,patientccc,batchno,supporttype,suppression_status) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, id); conn.pst.setString(2, facilityID); conn.pst.setInt(3, year); conn.pst.setInt(4, quarter); conn.pst.setInt(5, mflcode); conn.pst.setString(6, sex); conn.pst.setInt(7, age); conn.pst.setString(8, agebracket); conn.pst.setString(9, facilityName); conn.pst.setString(10, dateoftesting); conn.pst.setString(11, patientccc); conn.pst.setString(12, batchno); conn.pst.setString(13, supporttype); conn.pst.setString(14, suppression); conn.pst.executeUpdate(); added++; } else { //id,SubPartnerID,Year,Quarter,Mflcode,Sex ,age,agebracket,SubPartnerNom,dateoftesting,patientccc,batchno,supporttype String inserter = " UPDATE viral_load_raw SET SubPartnerID=?,Year=?,Quarter=?,Mflcode =?,Sex=?,age=?,agebracket=?,SubPartnerNom=?,dateoftesting=?,patientccc=?,batchno=?,supporttype=?,suppression_status=? " + " WHERE id=?"; conn.pst = conn.conn.prepareStatement(inserter); conn.pst.setString(1, facilityID); conn.pst.setInt(2, year); conn.pst.setInt(3, quarter); conn.pst.setInt(4, mflcode); conn.pst.setString(5, sex); conn.pst.setInt(6, age); conn.pst.setString(7, agebracket); conn.pst.setString(8, facilityName); conn.pst.setString(9, dateoftesting); conn.pst.setString(10, patientccc); conn.pst.setString(11, batchno); conn.pst.setString(12, supporttype); conn.pst.setString(13, suppression); conn.pst.setString(14, id); conn.pst.executeUpdate(); updated++; } } else { missing++; // missing facilities missingFacility += "facility name : " + facilityName + " mfl code : " + mflcode + " excel row num : " + i + "<br>"; System.out.println(facilityName + "facility is missing mflcode on subpartner :" + mflcode); } i++; } } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.pst != null) { conn.pst.close(); } } catch (SQLException ex) { Logger.getLogger(loadTBExcel.class.getName()).log(Level.SEVERE, null, ex); } String sessionText = "<br/><b> " + added + "</b> New data added <br/> <b> " + updated + "</b> updated facilities<br> <br> <b>" + missing + "</b> sites not in Imis Facilities List "; session.setAttribute("upload_success", sessionText); response.sendRedirect(nextpage); }