List of usage examples for org.apache.poi.ss.usermodel CellStyle setAlignment
void setAlignment(HorizontalAlignment align);
From source file:reports.Reached_OthersMessages.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException, InvalidFormatException { session = request.getSession();/*from ww w . ja v a 2s .c o m*/ dbConn conn = new dbConn(); ouputyear = outputMonth = ""; if (session.getAttribute("PepfarYear") != null) { year = Integer.parseInt(session.getAttribute("PepfarYear").toString()); quarter = Integer.parseInt(session.getAttribute("period").toString()); // year=2015; // quarter=2; System.out.println("dates are : " + year + " quarter : " + quarter); if (quarter == 4) { reportYear = year - 1; startmonth = "10"; endMonth = "12"; prevReportYear = reportYear; prevReportMonth = "09"; } else { reportYear = year; if (quarter == 1) { startmonth = "01"; endMonth = "03"; prevReportYear = reportYear - 1; prevReportMonth = "12"; } if (quarter == 2) { startmonth = "04"; endMonth = "06"; prevReportYear = reportYear; prevReportMonth = "03"; } if (quarter == 3) { startmonth = "07"; endMonth = "09"; prevReportYear = reportYear; prevReportMonth = "06"; } } startDate = "" + startmonth + "/01/" + reportYear + ""; endDate = "" + endMonth + "/31/" + reportYear + ""; startDate1 = "" + reportYear + "-" + startmonth + "-01"; endDate1 = "" + reportYear + "-" + endMonth + "-31"; endYearMonth = prevReportYear + "" + prevReportMonth; position = 1; // ouputyear=""+reportYear; if (quarter == 1) { outputMonth = "Jan_March_" + reportYear; } if (quarter == 2) { outputMonth = "Apr_Jun_" + reportYear; } if (quarter == 3) { outputMonth = "July_Sept_" + reportYear; } if (quarter == 4) { outputMonth = "Oct_Dec_" + reportYear; } String reportHeader[] = ("COUNTY NAME ,PARTNER NAME,DISTRICT NAME,DIC NAME, GROUP NAME,CLIENT NAME , AGE BRACKET, GENDER,YEAR,MONTH") .split(","); // COPY FILE TO BE WRITTEN TO Path original = Paths.get(getServletContext().getRealPath("/OTHER_MESSAGES.xlsm")); //original file Path destination = Paths.get(getServletContext().getRealPath("/OTHER_MESSAGES_1.xlsm")); //new file System.out.println("origin : " + original + " destination : " + destination); try { Files.copy(original, destination, StandardCopyOption.REPLACE_EXISTING); System.out.println("file copied----------------"); } catch (IOException x) { //catch all for IO problems System.out.println("fine not copied"); } String allpath = getServletContext().getRealPath("/OTHER_MESSAGES_1.xlsm"); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ XSSFWorkbook wb; OPCPackage pkg = OPCPackage.open(allpath); wb = new XSSFWorkbook(pkg); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ // HSSFWorkbook wb=new HSSFWorkbook(); XSSFSheet shet1 = wb.getSheet("Sheet1"); XSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); XSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); for (int i = 0; i <= reportHeader.length; i++) { shet1.setColumnWidth(i, 4000); } XSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); XSSFCell cell; XSSFRow rw0 = shet1.createRow(0); rw0.setHeightInPoints(30); rw0.setRowStyle(style2); for (int i = 0; i <= (reportHeader.length - 1); i++) { cell = rw0.createCell(i); cell.setCellValue(reportHeader[i]); cell.setCellStyle(stylex); } String query = "SELECT CLIENT, " + "" + "if( (dateRegister BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),dateRegister,dateAdherence) REPORTDATE," + " " + "dateRegister,dateAdherence, countyName, partnerName, districtName, AGEBRACKET, SEX,fname,mname,lname,GROUPNAME,DIC FROM (" + "SELECT DISTINCT(tempData.clientID) as CLIENT,if( (reg2Date BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),reg2Date,'0') dateRegister,if( (AdherenceDate BETWEEN '" + startDate1 + "' AND '" + endDate1 + "'),AdherenceDate,'0') dateAdherence, countyName, partnerName, districtName, AGEBRACKET, SEX,fname,mname,lname,GROUPNAME,DIC FROM (" + "SELECT DISTINCT(personal_information.client_id) as clientID,STR_TO_DATE(register2.date,'%m/%d/%Y') as reg2Date,STR_TO_DATE(adherence.date_of_session,'%m/%d/%Y') as AdherenceDate," + " CONCAT(personal_information.completionyear,personal_information.completionmonth) AS YEARMONTH," + "county.county_name AS countyName,partner.partner_name AS partnerName,district.district_name AS districtName," + "CASE " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 0 AND 9 THEN '0-9' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 10 AND 14 THEN '10-14' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 15 AND 19 THEN '15-19' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 20 AND 24 THEN '20-24' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) BETWEEN 25 AND 49 THEN '25-49' " + " WHEN (DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )) >49 THEN '50 and above' " + " ELSE 'NO DATE OF BIRTH'" + " END AS AGEBRACKET," + " CASE " + "when personal_information.gender LIKE 'Female' THEN 'F' " + "when personal_information.gender LIKE 'Male' THEN 'M' " + "ELSE 'NO SEX' " + "END AS SEX,personal_information.fname as fname,personal_information.mname as mname ,personal_information.lname as lname," + "groups.group_name as GROUPNAME,dic.dic_name as DIC " + " FROM personal_information " + "LEFT JOIN register2 ON personal_information.client_id=register2.client_id " + "LEFT JOIN adherence ON personal_information.client_id=adherence.client_id " + "LEFT JOIN groups ON personal_information.group_id=groups.group_id " + "LEFT JOIN dic ON personal_information.dic_id=dic.dic_id " + "LEFT JOIN district ON personal_information.district_id=district.district_id " + "LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + "LEFT JOIN county ON district.county_id=county.county_id " + "WHERE personal_information.completionyear>0 " + "&& (register2.datekey BETWEEN '" + startDate1.replace("-", "") + "' AND '" + endDate1.replace("-", "") + "' || STR_TO_DATE(adherence.date_of_session,'%m/%d/%Y') BETWEEN STR_TO_DATE('" + startDate + "','%m/%d/%Y') AND STR_TO_DATE('" + endDate + "','%m/%d/%Y'))" + " HAVING YEARMONTH<=" + endYearMonth + ") AS tempData GROUP BY tempData.clientID) AS finalTable "; System.out.println(query); conn.rs = conn.st.executeQuery(query); while (conn.rs.next()) { countyName = conn.rs.getString(5); partnerName = conn.rs.getString(6); districtName = conn.rs.getString(7); reportDate = conn.rs.getString(2); ageBracket = conn.rs.getString(8); gender = conn.rs.getString(9); fname = conn.rs.getString(10); mname = conn.rs.getString(11); lname = conn.rs.getString(12); if (conn.rs.getString(13) != null) { groupName = conn.rs.getString(13); } else { groupName = "Individual"; } if (conn.rs.getString(14) != null) { groupName = conn.rs.getString(14); } else { dic_name = "NO DIC"; } if (mname.equals(lname)) { mname = ""; } fullName = fname + " " + mname + " " + lname; String dateSplit[] = reportDate.split("-"); if (dateSplit[1].equals("01")) { month = "Jan"; } if (dateSplit[1].equals("02")) { month = "Feb"; } if (dateSplit[1].equals("03")) { month = "Mar"; } if (dateSplit[1].equals("04")) { month = "Apr"; } if (dateSplit[1].equals("05")) { month = "May"; } if (dateSplit[1].equals("06")) { month = "Jun"; } if (dateSplit[1].equals("07")) { month = "Jul"; } if (dateSplit[1].equals("08")) { month = "Aug"; } if (dateSplit[1].equals("09")) { month = "Sept"; } if (dateSplit[1].equals("10")) { month = "Oct"; } if (dateSplit[1].equals("11")) { month = "Nov"; } if (dateSplit[1].equals("12")) { month = "Dec"; } year2 = dateSplit[0]; String data[] = (countyName + "," + partnerName + "," + districtName + "," + dic_name + "," + groupName + "," + fullName + "," + ageBracket + "," + gender + "," + year2 + "," + month) .split(","); XSSFRow rw1 = shet1.createRow(position); rw1.setHeightInPoints(25); rw1.setRowStyle(style2); for (int i = 0; i <= (data.length - 1); i++) { cell = rw1.createCell(i); cell.setCellValue(data[i]); cell.setCellStyle(styleBorder); } position++; } IdGenerator CRT = new IdGenerator(); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_REACHED_WITH_OTHER_MESSAGES_REPORT_FOR_" + outputMonth + "_CREATED_ON_" + CRT.timestamp() + ".xlsm"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); pkg.close(); } else { session.setAttribute("kePMSError", "<font color=\"red\">Error : Please try again.</font>"); response.sendRedirect("kePMS.jsp"); } }
From source file:reports.receivedMessageDIC.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/* www. j a v a2s. c om*/ dbConn conn = new dbConn(); i = 4; startDate = session.getAttribute("custstartDate").toString(); endDate = session.getAttribute("custendDate").toString(); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); shet1.setColumnWidth(0, 5000); shet1.setColumnWidth(1, 5000); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5500); shet1.setColumnWidth(4, 10); shet1.setColumnWidth(5, 10); shet1.setColumnWidth(6, 10); shet1.setColumnWidth(7, 10); shet1.setColumnWidth(8, 10); shet1.setColumnWidth(9, 10); shet1.setColumnWidth(10, 10); shet1.setColumnWidth(11, 10); shet1.setColumnWidth(12, 10); shet1.setColumnWidth(13, 5000); shet1.setColumnWidth(14, 5300); shet1.setColumnWidth(15, 5000); shet1.setColumnWidth(16, 5200); shet1.setColumnWidth(17, 5200); shet1.setColumnWidth(18, 5200); shet1.setColumnWidth(19, 5800); shet1.setColumnWidth(20, 5000); shet1.setColumnWidth(21, 5300); shet1.setColumnWidth(22, 5300); shet1.setColumnWidth(23, 5000); shet1.setColumnWidth(24, 5200); shet1.setColumnWidth(25, 5200); // shet1.setColumnWidth(26, 5200); // shet1.setColumnWidth(27, 5800); // shet1.setColumnWidth(28, 5000); // shet1.setColumnWidth(29, 5300); // shet1.setColumnWidth(30, 5800); // shet1.setColumnWidth(31, 5000); // shet1.setColumnWidth(32, 5300); // shet1.setColumnWidth(20, 2000); HSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; HSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0, cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16, cell17, cell18, cell19, cell20; HSSFCell cell21, cell22, cell23, cell24, cell25, cell26, cell27, cell28, cell29, cell30, cell31, cell32, cell33; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); // cell4=rw4.createCell(4); // cell5=rw4.createCell(5); cell13 = rw4.createCell(13); cell14 = rw4.createCell(14); cell15 = rw4.createCell(15); cell16 = rw4.createCell(16); cell17 = rw4.createCell(17); cell18 = rw4.createCell(18); cell19 = rw4.createCell(19); cell20 = rw4.createCell(20); cell21 = rw4.createCell(21); cell22 = rw4.createCell(22); cell23 = rw4.createCell(23); cell24 = rw4.createCell(24); cell25 = rw4.createCell(25); // // cell26=rw4.createCell(26); // cell27=rw4.createCell(27); // cell28=rw4.createCell(28); // cell29=rw4.createCell(29); // cell30=rw4.createCell(30); // cell31=rw4.createCell(31); // cell32=rw4.createCell(32); // cell33=rw4.createCell(33); cell0.setCellValue("COUNTY NAME"); cell1.setCellValue("PARTNER NAME"); cell2.setCellValue("DISTRICT NAME"); cell3.setCellValue("DIC"); // cell4.setCellValue("MESSAGE NO"); // cell5.setCellValue("ACHIEVED"); cell13.setCellValue("Knowledge of HIV Status"); cell14.setCellValue("Partner HIV Testing"); cell15.setCellValue("Child HIV Testing"); cell16.setCellValue("Discordance"); cell17.setCellValue("HIV Disclosure"); cell18.setCellValue("Risk Factor/Reduction"); cell19.setCellValue("Condom Use"); cell20.setCellValue("Alcohol and Substance Abuse"); cell21.setCellValue("Adherence"); cell22.setCellValue("STIs"); cell23.setCellValue("Family Planning"); cell24.setCellValue("PMTCT"); cell25.setCellValue("TB"); // cell26.setCellValue("Received Contraceptives"); // cell27.setCellValue("Reffered To Service Point"); // cell28.setCellValue("Given Condoms"); // cell29 .setCellValue("Screened For TB"); // cell30.setCellValue("Screened For STIs"); // cell31.setCellValue("Partner Tested"); // cell32.setCellValue("Children Tested"); // cell33.setCellValue("Disclosed Status"); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); cell0.setCellStyle(stylex); cell1.setCellStyle(stylex); cell2.setCellStyle(stylex); cell3.setCellStyle(stylex); //cell4.setCellStyle(stylex); //cell5.setCellStyle(stylex); //cell6.setCellStyle(stylex); //cell7.setCellStyle(stylex); //cell8.setCellStyle(stylex); //cell9.setCellStyle(stylex); //cell10.setCellStyle(stylex); //cell11.setCellStyle(stylex); //cell12.setCellStyle(stylex); cell13.setCellStyle(stylex); cell14.setCellStyle(stylex); cell15.setCellStyle(stylex); cell16.setCellStyle(stylex); cell17.setCellStyle(stylex); cell18.setCellStyle(stylex); cell19.setCellStyle(stylex); cell20.setCellStyle(stylex); cell21.setCellStyle(stylex); cell22.setCellStyle(stylex); cell23.setCellStyle(stylex); cell24.setCellStyle(stylex); cell25.setCellStyle(stylex); //cell26.setCellStyle(stylex); //cell27.setCellStyle(stylex); //cell28.setCellStyle(stylex); //cell29.setCellStyle(stylex); //cell30.setCellStyle(stylex); //cell31.setCellStyle(stylex); //cell32.setCellStyle(stylex); //cell33.setCellStyle(stylex); i = 1; current_group = ""; String getClients = "SELECT " + "DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )" + ",personal_information.gender,district.district_name,partner.partner_name,county.county_name," + "register2.session_no,SUM(register2.value),dic.dic_name " + " FROM personal_information " + "LEFT JOIN dic ON personal_information.dic_id=dic.dic_id " + "LEFT JOIN district ON personal_information.district_id=district.district_id " + "LEFT JOIN county ON district.county_id=county.county_id " + "LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + "LEFT JOIN register2 ON personal_information.client_id=register2.client_id " + " WHERE register2.value<2 && STR_TO_DATE(register2.date,'%m/%d/%Y') BETWEEN STR_TO_DATE('" + startDate + "','%m/%d/%Y') AND STR_TO_DATE('" + endDate + "','%m/%d/%Y') " + " GROUP BY county.county_name,partner.partner_name,district.district_name,dic.dic_name,register2.session_no ORDER BY partner.partner_name,district.district_name,dic.dic_name"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { county = district = hf = partner = groupname = serviceprovider = clientname = age = gender = groupings = year = providerid = ""; countyid = districtid = hfid = partnerid = groupid = serviceproviderid = clientid = ""; s1 = s2 = s3 = s4 = s5 = s6 = s7 = s8 = s9 = s10 = s11 = s12 = s13 = ""; cm = rsp = cd = tb = sti = testedpartner = testedchild = session_no = value = status; sess = val = cds = 0; hf_id = lessons_attended = national_id = ccc_no = mobile_no = dob = ""; added = 0; message_no = achieved = 0; dic_name = ""; age = conn.rs.getString(1); gender = conn.rs.getString(2); district = conn.rs.getString(3); partner = conn.rs.getString(4); county = conn.rs.getString(5); message_no = conn.rs.getInt(6); achieved = conn.rs.getInt(7); dic_name = conn.rs.getString(8); // OUTPUT ATTENDED-------------------------------- current_group = district; cm = rsp = tb = sti = testedpartner = testedchild = session_no = value = status = "NO"; if (current_group.equals("")) { previous_group = current_group; // OUTPUT SERVICES PROVIDED================================ HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; HSSFCell cell21x, cell22x, cell23x, cell24x, cell25x, cell26x, cell27x, cell28x, cell29x, cell30x, cell31x, cell32x, cell33x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); // cell4x=rw4x.createCell(4); // cell5x=rw4x.createCell(5); cell13x = rw4x.createCell(13); cell14x = rw4x.createCell(14); cell15x = rw4x.createCell(15); cell16x = rw4x.createCell(16); cell17x = rw4x.createCell(17); cell18x = rw4x.createCell(18); cell19x = rw4x.createCell(19); cell20x = rw4x.createCell(20); cell21x = rw4x.createCell(21); cell22x = rw4x.createCell(22); cell23x = rw4x.createCell(23); cell24x = rw4x.createCell(24); cell25x = rw4x.createCell(25); // cell26x=rw4x.createCell(26); // cell27x=rw4x.createCell(27); // cell28x=rw4x.createCell(28); // cell29x=rw4x.createCell(29); // cell30x=rw4x.createCell(30); // cell31x=rw4x.createCell(31); // cell32x=rw4x.createCell(32); // cell33x=rw4x.createCell(33); cell0x.setCellValue(county); cell1x.setCellValue(partner); cell2x.setCellValue(district); cell3x.setCellValue(dic_name); // cell4x.setCellValue(message_no); // cell5x.setCellValue(achieved); if (message_no == 1) { cell13x.setCellValue(achieved); } if (message_no == 2) { cell14x.setCellValue(achieved); } if (message_no == 3) { cell15x.setCellValue(achieved); } if (message_no == 4) { cell16x.setCellValue(achieved); } if (message_no == 5) { cell17x.setCellValue(achieved); } if (message_no == 6) { cell18x.setCellValue(achieved); } if (message_no == 7) { cell19x.setCellValue(achieved); } if (message_no == 8) { cell20x.setCellValue(achieved); } if (message_no == 9) { cell21x.setCellValue(achieved); } if (message_no == 10) { cell22x.setCellValue(achieved); } if (message_no == 11) { cell23x.setCellValue(achieved); } if (message_no == 12) { cell24x.setCellValue(achieved); } if (message_no == 13) { cell25x.setCellValue(achieved); } // cell26x .setCellValue(cm); // cell27x.setCellValue(rsp); // cell28x.setCellValue(cds); // cell29x.setCellValue(tb); // cell30x.setCellValue(sti); // cell31x.setCellValue(testedpartner); // cell32x.setCellValue(testedchild); // cell33x.setCellValue(status); cell0x.setCellStyle(styleBorder); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell3x.setCellStyle(styleBorder); //cell4x.setCellStyle(styleBorder); //cell5x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); cell14x.setCellStyle(styleBorder); cell15x.setCellStyle(styleBorder); cell16x.setCellStyle(styleBorder); cell17x.setCellStyle(styleBorder); cell18x.setCellStyle(styleBorder); cell19x.setCellStyle(styleBorder); cell20x.setCellStyle(styleBorder); cell21x.setCellStyle(styleBorder); cell22x.setCellStyle(styleBorder); cell23x.setCellStyle(styleBorder); cell24x.setCellStyle(styleBorder); cell25x.setCellStyle(styleBorder); //cell26x.setCellStyle(styleBorder); //cell27x.setCellStyle(styleBorder); //cell28x.setCellStyle(styleBorder); //cell29x.setCellStyle(styleBorder); //cell30x.setCellStyle(styleBorder); //cell31x.setCellStyle(styleBorder); //cell32x.setCellStyle(styleBorder); //cell33x.setCellStyle(styleBorder); i++; } else if (!current_group.equals(previous_group)) { // update attended sessions; previous_group = current_group; // OUTPUT SERVICES PROVIDED================================ HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; HSSFCell cell21x, cell22x, cell23x, cell24x, cell25x, cell26x, cell27x, cell28x, cell29x, cell30x, cell31x, cell32x, cell33x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); // cell4x=rw4x.createCell(4); // cell5x=rw4x.createCell(5); cell13x = rw4x.createCell(13); cell14x = rw4x.createCell(14); cell15x = rw4x.createCell(15); cell16x = rw4x.createCell(16); cell17x = rw4x.createCell(17); cell18x = rw4x.createCell(18); cell19x = rw4x.createCell(19); cell20x = rw4x.createCell(20); cell21x = rw4x.createCell(21); cell22x = rw4x.createCell(22); cell23x = rw4x.createCell(23); cell24x = rw4x.createCell(24); cell25x = rw4x.createCell(25); // cell26x=rw4x.createCell(26); // cell27x=rw4x.createCell(27); // cell28x=rw4x.createCell(28); // cell29x=rw4x.createCell(29); // cell30x=rw4x.createCell(30); // cell31x=rw4x.createCell(31); // cell32x=rw4x.createCell(32); // cell33x=rw4x.createCell(33); cell0x.setCellValue(county); cell1x.setCellValue(partner); cell2x.setCellValue(district); cell3x.setCellValue(dic_name); // cell4x.setCellValue(message_no); // cell5x.setCellValue(achieved); if (message_no == 1) { cell13x.setCellValue(achieved); } if (message_no == 2) { cell14x.setCellValue(achieved); } if (message_no == 3) { cell15x.setCellValue(achieved); } if (message_no == 4) { cell16x.setCellValue(achieved); } if (message_no == 5) { cell17x.setCellValue(achieved); } if (message_no == 6) { cell18x.setCellValue(achieved); } if (message_no == 7) { cell19x.setCellValue(achieved); } if (message_no == 8) { cell20x.setCellValue(achieved); } if (message_no == 9) { cell21x.setCellValue(achieved); } if (message_no == 10) { cell22x.setCellValue(achieved); } if (message_no == 11) { cell23x.setCellValue(achieved); } if (message_no == 12) { cell24x.setCellValue(achieved); } if (message_no == 13) { cell25x.setCellValue(achieved); } // cell26x .setCellValue(cm); // cell27x.setCellValue(rsp); // cell28x.setCellValue(cds); // cell29x.setCellValue(tb); // cell30x.setCellValue(sti); // cell31x.setCellValue(testedpartner); // cell32x.setCellValue(testedchild); // cell33x.setCellValue(status); cell0x.setCellStyle(styleBorder); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell3x.setCellStyle(styleBorder); //cell4x.setCellStyle(styleBorder); //cell5x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); cell14x.setCellStyle(styleBorder); cell15x.setCellStyle(styleBorder); cell16x.setCellStyle(styleBorder); cell17x.setCellStyle(styleBorder); cell18x.setCellStyle(styleBorder); cell19x.setCellStyle(styleBorder); cell20x.setCellStyle(styleBorder); cell21x.setCellStyle(styleBorder); cell22x.setCellStyle(styleBorder); cell23x.setCellStyle(styleBorder); cell24x.setCellStyle(styleBorder); cell25x.setCellStyle(styleBorder); //cell26x.setCellStyle(styleBorder); //cell27x.setCellStyle(styleBorder); //cell28x.setCellStyle(styleBorder); //cell29x.setCellStyle(styleBorder); //cell30x.setCellStyle(styleBorder); //cell31x.setCellStyle(styleBorder); //cell32x.setCellStyle(styleBorder); //cell33x.setCellStyle(styleBorder); i++; } else if (current_group.equals(previous_group)) { HSSFRow rw4x = shet1.getRow(i - 1); if (message_no == 1) { HSSFCell cell13x = rw4x.createCell(13); cell13x.setCellValue(achieved); cell13x.setCellStyle(styleBorder); } if (message_no == 2) { HSSFCell cell14x = rw4x.createCell(14); cell14x.setCellValue(achieved); cell14x.setCellStyle(styleBorder); } if (message_no == 3) { HSSFCell cell15x = rw4x.createCell(15); cell15x.setCellValue(achieved); cell15x.setCellStyle(styleBorder); } if (message_no == 4) { HSSFCell cell16x = rw4x.createCell(16); cell16x.setCellValue(achieved); cell16x.setCellStyle(styleBorder); } if (message_no == 5) { HSSFCell cell17x = rw4x.createCell(17); cell17x.setCellValue(achieved); cell17x.setCellStyle(styleBorder); } if (message_no == 6) { HSSFCell cell18x = rw4x.createCell(18); cell18x.setCellValue(achieved); cell18x.setCellStyle(styleBorder); } if (message_no == 7) { HSSFCell cell19x = rw4x.createCell(19); cell19x.setCellValue(achieved); cell19x.setCellStyle(styleBorder); } if (message_no == 8) { HSSFCell cell20x = rw4x.createCell(20); cell20x.setCellValue(achieved); cell20x.setCellStyle(styleBorder); } if (message_no == 9) { HSSFCell cell21x = rw4x.createCell(21); cell21x.setCellValue(achieved); cell21x.setCellStyle(styleBorder); } if (message_no == 10) { HSSFCell cell22x = rw4x.createCell(22); cell22x.setCellValue(achieved); cell22x.setCellStyle(styleBorder); } if (message_no == 11) { HSSFCell cell23x = rw4x.createCell(23); cell23x.setCellValue(achieved); cell23x.setCellStyle(styleBorder); } if (message_no == 12) { HSSFCell cell24x = rw4x.createCell(24); cell24x.setCellValue(achieved); cell24x.setCellStyle(styleBorder); } if (message_no == 13) { HSSFCell cell25x = rw4x.createCell(25); cell25x.setCellValue(achieved); cell25x.setCellStyle(styleBorder); } } else { System.out.println("here-------------nothing seen"); } } System.out.println("here : " + i); if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_Raw_Data_DIC.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.receivedMessageDistrict.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/* w ww.j av a2s.co m*/ dbConn conn = new dbConn(); startDate = session.getAttribute("custstartDate").toString(); endDate = session.getAttribute("custendDate").toString(); total = 0; i = 4; previousPartner = currentPartner = ""; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); shet1.setColumnWidth(0, 5000); shet1.setColumnWidth(1, 5000); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 10); shet1.setColumnWidth(4, 10); shet1.setColumnWidth(5, 10); shet1.setColumnWidth(6, 10); shet1.setColumnWidth(7, 10); shet1.setColumnWidth(8, 10); shet1.setColumnWidth(9, 10); shet1.setColumnWidth(10, 10); shet1.setColumnWidth(11, 10); shet1.setColumnWidth(12, 10); shet1.setColumnWidth(13, 5000); shet1.setColumnWidth(14, 5300); shet1.setColumnWidth(15, 5000); shet1.setColumnWidth(16, 5200); shet1.setColumnWidth(17, 5200); shet1.setColumnWidth(18, 5200); shet1.setColumnWidth(19, 5800); shet1.setColumnWidth(20, 5000); shet1.setColumnWidth(21, 5300); shet1.setColumnWidth(22, 5300); shet1.setColumnWidth(23, 5000); shet1.setColumnWidth(24, 5200); shet1.setColumnWidth(25, 5200); HSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; HSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0, cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16, cell17, cell18, cell19, cell20; HSSFCell cell21, cell22, cell23, cell24, cell25, cell26, cell27, cell28, cell29, cell30, cell31, cell32, cell33; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell13 = rw4.createCell(13); cell14 = rw4.createCell(14); cell15 = rw4.createCell(15); cell16 = rw4.createCell(16); cell17 = rw4.createCell(17); cell18 = rw4.createCell(18); cell19 = rw4.createCell(19); cell20 = rw4.createCell(20); cell21 = rw4.createCell(21); cell22 = rw4.createCell(22); cell23 = rw4.createCell(23); cell24 = rw4.createCell(24); cell25 = rw4.createCell(25); cell0.setCellValue("COUNTY NAME"); cell1.setCellValue("PARTNER NAME"); cell2.setCellValue("DISTRICT NAME"); cell13.setCellValue("Knowledge of HIV Status"); cell14.setCellValue("Partner HIV Testing"); cell15.setCellValue("Child HIV Testing"); cell16.setCellValue("Discordance"); cell17.setCellValue("HIV Disclosure"); cell18.setCellValue("Risk Factor/Reduction"); cell19.setCellValue("Condom Use"); cell20.setCellValue("Alcohol and Substance Abuse"); cell21.setCellValue("Adherence"); cell22.setCellValue("STIs"); cell23.setCellValue("Family Planning"); cell24.setCellValue("PMTCT"); cell25.setCellValue("TB"); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); cell0.setCellStyle(stylex); cell1.setCellStyle(stylex); cell2.setCellStyle(stylex); cell13.setCellStyle(stylex); cell14.setCellStyle(stylex); cell15.setCellStyle(stylex); cell16.setCellStyle(stylex); cell17.setCellStyle(stylex); cell18.setCellStyle(stylex); cell19.setCellStyle(stylex); cell20.setCellStyle(stylex); cell21.setCellStyle(stylex); cell22.setCellStyle(stylex); cell23.setCellStyle(stylex); cell24.setCellStyle(stylex); cell25.setCellStyle(stylex); i = 1; previous_group = ""; previousPartner = ""; current_group = ""; String getClients = "SELECT district.district_name,partner.partner_name,county.county_name," + "register2.session_no,SUM(register2.value) " + " FROM personal_information " + "LEFT JOIN district ON personal_information.district_id=district.district_id " + "LEFT JOIN county ON district.county_id=county.county_id " + "LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + "LEFT JOIN register2 ON personal_information.client_id=register2.client_id " + " WHERE register2.value<2 && STR_TO_DATE(register2.date,'%m/%d/%Y') BETWEEN STR_TO_DATE('" + startDate + "','%m/%d/%Y') AND STR_TO_DATE('" + endDate + "','%m/%d/%Y') " + " GROUP BY county.county_name,district.district_name,register2.session_no ORDER BY county.county_name,partner.partner_name,district.district_name"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { county = district = hf = partner = groupname = serviceprovider = clientname = age = gender = groupings = year = providerid = ""; countyid = districtid = hfid = partnerid = groupid = serviceproviderid = clientid = ""; s1 = s2 = s3 = s4 = s5 = s6 = s7 = s8 = s9 = s10 = s11 = s12 = s13 = ""; cm = rsp = cd = tb = sti = testedpartner = testedchild = session_no = value = status; sess = val = cds = 0; hf_id = lessons_attended = national_id = ccc_no = mobile_no = dob = ""; added = 0; message_no = achieved = 0; // age=conn.rs.getString(1); // gender=conn.rs.getString(2); district = conn.rs.getString(1); partner = conn.rs.getString(2); System.out.println("ppartner is : " + partner); if (partner == null) { partner = "NO PARTNER"; } county = conn.rs.getString(3); message_no = conn.rs.getInt(4); achieved = conn.rs.getInt(5); if (message_no == 5) { total += achieved; } // OUTPUT ATTENDED-------------------------------- current_group = district; currentPartner = partner; System.out.println("current partner : " + currentPartner + " previous partner : " + previousPartner); cm = rsp = tb = sti = testedpartner = testedchild = session_no = value = status = "NO"; System.out.println("current partner : " + currentPartner + " previous partner : " + previousPartner + " achieved : " + achieved); System.out.println("current district : " + current_group + " previous distrcit : " + previous_group + "mesage no : " + message_no); System.out.println( "------------------------------------------------------------------------------------------------------"); if (previous_group.equals("")) { previous_group = current_group; previousPartner = currentPartner; // OUTPUT SERVICES PROVIDED================================ HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; HSSFCell cell21x, cell22x, cell23x, cell24x, cell25x, cell26x, cell27x, cell28x, cell29x, cell30x, cell31x, cell32x, cell33x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell13x = rw4x.createCell(13); cell14x = rw4x.createCell(14); cell15x = rw4x.createCell(15); cell16x = rw4x.createCell(16); cell17x = rw4x.createCell(17); cell18x = rw4x.createCell(18); cell19x = rw4x.createCell(19); cell20x = rw4x.createCell(20); cell21x = rw4x.createCell(21); cell22x = rw4x.createCell(22); cell23x = rw4x.createCell(23); cell24x = rw4x.createCell(24); cell25x = rw4x.createCell(25); cell0x.setCellValue(county); cell1x.setCellValue(partner); cell2x.setCellValue(district); if (message_no == 1) { cell13x.setCellValue(achieved); } if (message_no == 2) { cell14x.setCellValue(achieved); } if (message_no == 3) { cell15x.setCellValue(achieved); } if (message_no == 4) { cell16x.setCellValue(achieved); } if (message_no == 5) { cell17x.setCellValue(achieved); } if (message_no == 6) { cell18x.setCellValue(achieved); } if (message_no == 7) { cell19x.setCellValue(achieved); } if (message_no == 8) { cell20x.setCellValue(achieved); } if (message_no == 9) { cell21x.setCellValue(achieved); } if (message_no == 10) { cell22x.setCellValue(achieved); } if (message_no == 11) { cell23x.setCellValue(achieved); } if (message_no == 12) { cell24x.setCellValue(achieved); } if (message_no == 13) { cell25x.setCellValue(achieved); } cell0x.setCellStyle(styleBorder); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); cell14x.setCellStyle(styleBorder); cell15x.setCellStyle(styleBorder); cell16x.setCellStyle(styleBorder); cell17x.setCellStyle(styleBorder); cell18x.setCellStyle(styleBorder); cell19x.setCellStyle(styleBorder); cell20x.setCellStyle(styleBorder); cell21x.setCellStyle(styleBorder); cell22x.setCellStyle(styleBorder); cell23x.setCellStyle(styleBorder); cell24x.setCellStyle(styleBorder); cell25x.setCellStyle(styleBorder); i++; } else if (!currentPartner.equals(previousPartner)) { previous_group = current_group; previousPartner = currentPartner; // OUTPUT SERVICES PROVIDED================================ HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; HSSFCell cell21x, cell22x, cell23x, cell24x, cell25x, cell26x, cell27x, cell28x, cell29x, cell30x, cell31x, cell32x, cell33x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell13x = rw4x.createCell(13); cell14x = rw4x.createCell(14); cell15x = rw4x.createCell(15); cell16x = rw4x.createCell(16); cell17x = rw4x.createCell(17); cell18x = rw4x.createCell(18); cell19x = rw4x.createCell(19); cell20x = rw4x.createCell(20); cell21x = rw4x.createCell(21); cell22x = rw4x.createCell(22); cell23x = rw4x.createCell(23); cell24x = rw4x.createCell(24); cell25x = rw4x.createCell(25); cell0x.setCellValue(county); cell1x.setCellValue(partner); cell2x.setCellValue(district); if (message_no == 1) { cell13x.setCellValue(achieved); } if (message_no == 2) { cell14x.setCellValue(achieved); } if (message_no == 3) { cell15x.setCellValue(achieved); } if (message_no == 4) { cell16x.setCellValue(achieved); } if (message_no == 5) { cell17x.setCellValue(achieved); } if (message_no == 6) { cell18x.setCellValue(achieved); } if (message_no == 7) { cell19x.setCellValue(achieved); } if (message_no == 8) { cell20x.setCellValue(achieved); } if (message_no == 9) { cell21x.setCellValue(achieved); } if (message_no == 10) { cell22x.setCellValue(achieved); } if (message_no == 11) { cell23x.setCellValue(achieved); } if (message_no == 12) { cell24x.setCellValue(achieved); } if (message_no == 13) { cell25x.setCellValue(achieved); } cell0x.setCellStyle(styleBorder); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); cell14x.setCellStyle(styleBorder); cell15x.setCellStyle(styleBorder); cell16x.setCellStyle(styleBorder); cell17x.setCellStyle(styleBorder); cell18x.setCellStyle(styleBorder); cell19x.setCellStyle(styleBorder); cell20x.setCellStyle(styleBorder); cell21x.setCellStyle(styleBorder); cell22x.setCellStyle(styleBorder); cell23x.setCellStyle(styleBorder); cell24x.setCellStyle(styleBorder); cell25x.setCellStyle(styleBorder); i++; } else if (!current_group.equals(previous_group)) { previous_group = current_group; previousPartner = currentPartner; // update attended sessions; // previous_group=current_group; // OUTPUT SERVICES PROVIDED================================ HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; HSSFCell cell21x, cell22x, cell23x, cell24x, cell25x, cell26x, cell27x, cell28x, cell29x, cell30x, cell31x, cell32x, cell33x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell13x = rw4x.createCell(13); cell14x = rw4x.createCell(14); cell15x = rw4x.createCell(15); cell16x = rw4x.createCell(16); cell17x = rw4x.createCell(17); cell18x = rw4x.createCell(18); cell19x = rw4x.createCell(19); cell20x = rw4x.createCell(20); cell21x = rw4x.createCell(21); cell22x = rw4x.createCell(22); cell23x = rw4x.createCell(23); cell24x = rw4x.createCell(24); cell25x = rw4x.createCell(25); cell0x.setCellValue(county); cell1x.setCellValue(partner); cell2x.setCellValue(district); if (message_no == 1) { cell13x.setCellValue(achieved); } if (message_no == 2) { cell14x.setCellValue(achieved); } if (message_no == 3) { cell15x.setCellValue(achieved); } if (message_no == 4) { cell16x.setCellValue(achieved); } if (message_no == 5) { cell17x.setCellValue(achieved); } if (message_no == 6) { cell18x.setCellValue(achieved); } if (message_no == 7) { cell19x.setCellValue(achieved); } if (message_no == 8) { cell20x.setCellValue(achieved); } if (message_no == 9) { cell21x.setCellValue(achieved); } if (message_no == 10) { cell22x.setCellValue(achieved); } if (message_no == 11) { cell23x.setCellValue(achieved); } if (message_no == 12) { cell24x.setCellValue(achieved); } if (message_no == 13) { cell25x.setCellValue(achieved); } cell0x.setCellStyle(styleBorder); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); cell14x.setCellStyle(styleBorder); cell15x.setCellStyle(styleBorder); cell16x.setCellStyle(styleBorder); cell17x.setCellStyle(styleBorder); cell18x.setCellStyle(styleBorder); cell19x.setCellStyle(styleBorder); cell20x.setCellStyle(styleBorder); cell21x.setCellStyle(styleBorder); cell22x.setCellStyle(styleBorder); cell23x.setCellStyle(styleBorder); cell24x.setCellStyle(styleBorder); cell25x.setCellStyle(styleBorder); i++; } else if (current_group.equals(previous_group) && currentPartner.equals(previousPartner)) { HSSFRow rw4x = shet1.getRow(i - 1); if (message_no == 1) { HSSFCell cell13x = rw4x.createCell(13); cell13x.setCellValue(achieved); cell13x.setCellStyle(styleBorder); } if (message_no == 2) { HSSFCell cell14x = rw4x.createCell(14); cell14x.setCellValue(achieved); cell14x.setCellStyle(styleBorder); } if (message_no == 3) { HSSFCell cell15x = rw4x.createCell(15); cell15x.setCellValue(achieved); cell15x.setCellStyle(styleBorder); } if (message_no == 4) { HSSFCell cell16x = rw4x.createCell(16); cell16x.setCellValue(achieved); cell16x.setCellStyle(styleBorder); } if (message_no == 5) { HSSFCell cell17x = rw4x.createCell(17); cell17x.setCellValue(achieved); cell17x.setCellStyle(styleBorder); } if (message_no == 6) { HSSFCell cell18x = rw4x.createCell(18); cell18x.setCellValue(achieved); cell18x.setCellStyle(styleBorder); } if (message_no == 7) { HSSFCell cell19x = rw4x.createCell(19); cell19x.setCellValue(achieved); cell19x.setCellStyle(styleBorder); } if (message_no == 8) { HSSFCell cell20x = rw4x.createCell(20); cell20x.setCellValue(achieved); cell20x.setCellStyle(styleBorder); } if (message_no == 9) { HSSFCell cell21x = rw4x.createCell(21); cell21x.setCellValue(achieved); cell21x.setCellStyle(styleBorder); } if (message_no == 10) { HSSFCell cell22x = rw4x.createCell(22); cell22x.setCellValue(achieved); cell22x.setCellStyle(styleBorder); } if (message_no == 11) { HSSFCell cell23x = rw4x.createCell(23); cell23x.setCellValue(achieved); cell23x.setCellStyle(styleBorder); } if (message_no == 12) { HSSFCell cell24x = rw4x.createCell(24); cell24x.setCellValue(achieved); cell24x.setCellStyle(styleBorder); } if (message_no == 13) { HSSFCell cell25x = rw4x.createCell(25); cell25x.setCellValue(achieved); cell25x.setCellStyle(styleBorder); } } else { System.out.println("here-------------nothing seen"); } } // System.out.println("total : "+total); if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_Raw_Data_DISTRICT.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.ReceivedMessages.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/*from w w w . j ava2s . c o m*/ dbConn conn = new dbConn(); startDate = session.getAttribute("custstartDate").toString(); endDate = session.getAttribute("custendDate").toString(); i = 4; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); shet1.setColumnWidth(0, 5000); shet1.setColumnWidth(1, 5000); shet1.setColumnWidth(2, 5000); shet1.setColumnWidth(3, 5500); shet1.setColumnWidth(4, 5500); shet1.setColumnWidth(5, 10); shet1.setColumnWidth(6, 10); shet1.setColumnWidth(7, 10); shet1.setColumnWidth(8, 10); shet1.setColumnWidth(9, 10); shet1.setColumnWidth(10, 10); shet1.setColumnWidth(11, 10); shet1.setColumnWidth(12, 10); shet1.setColumnWidth(13, 5000); shet1.setColumnWidth(14, 5300); shet1.setColumnWidth(15, 5000); shet1.setColumnWidth(16, 5200); shet1.setColumnWidth(17, 5200); shet1.setColumnWidth(18, 5200); shet1.setColumnWidth(19, 5800); shet1.setColumnWidth(20, 5000); shet1.setColumnWidth(21, 5300); shet1.setColumnWidth(22, 5300); shet1.setColumnWidth(23, 5000); shet1.setColumnWidth(24, 5200); shet1.setColumnWidth(25, 5200); // shet1.setColumnWidth(26, 5200); // shet1.setColumnWidth(27, 5800); // shet1.setColumnWidth(28, 5000); // shet1.setColumnWidth(29, 5300); // shet1.setColumnWidth(30, 5800); // shet1.setColumnWidth(31, 5000); // shet1.setColumnWidth(32, 5300); // shet1.setColumnWidth(20, 2000); HSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; HSSFRow rw4 = shet1.createRow(0); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0, cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14, cell15, cell16, cell17, cell18, cell19, cell20; HSSFCell cell21, cell22, cell23, cell24, cell25, cell26, cell27, cell28, cell29, cell30, cell31, cell32, cell33; cell0 = rw4.createCell(0); cell1 = rw4.createCell(1); cell2 = rw4.createCell(2); cell3 = rw4.createCell(3); cell4 = rw4.createCell(4); // cell5=rw4.createCell(5); cell13 = rw4.createCell(13); cell14 = rw4.createCell(14); cell15 = rw4.createCell(15); cell16 = rw4.createCell(16); cell17 = rw4.createCell(17); cell18 = rw4.createCell(18); cell19 = rw4.createCell(19); cell20 = rw4.createCell(20); cell21 = rw4.createCell(21); cell22 = rw4.createCell(22); cell23 = rw4.createCell(23); cell24 = rw4.createCell(24); cell25 = rw4.createCell(25); // // cell26=rw4.createCell(26); // cell27=rw4.createCell(27); // cell28=rw4.createCell(28); // cell29=rw4.createCell(29); // cell30=rw4.createCell(30); // cell31=rw4.createCell(31); // cell32=rw4.createCell(32); // cell33=rw4.createCell(33); cell0.setCellValue("COUNTY NAME"); cell1.setCellValue("PARTNER NAME"); cell2.setCellValue("DISTRICT NAME"); cell3.setCellValue("NEAREST FACILITY"); cell4.setCellValue("GROUP NAME"); // cell5.setCellValue("ACHIEVED"); cell13.setCellValue("Knowledge of HIV Status"); cell14.setCellValue("Partner HIV Testing"); cell15.setCellValue("Child HIV Testing"); cell16.setCellValue("Discordance"); cell17.setCellValue("HIV Disclosure"); cell18.setCellValue("Risk Factor/Reduction"); cell19.setCellValue("Condom Use"); cell20.setCellValue("Alcohol and Substance Abuse"); cell21.setCellValue("Adherence"); cell22.setCellValue("STIs"); cell23.setCellValue("Family Planning"); cell24.setCellValue("PMTCT"); cell25.setCellValue("TB"); // cell26.setCellValue("Received Contraceptives"); // cell27.setCellValue("Reffered To Service Point"); // cell28.setCellValue("Given Condoms"); // cell29 .setCellValue("Screened For TB"); // cell30.setCellValue("Screened For STIs"); // cell31.setCellValue("Partner Tested"); // cell32.setCellValue("Children Tested"); // cell33.setCellValue("Disclosed Status"); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); cell0.setCellStyle(stylex); cell1.setCellStyle(stylex); cell2.setCellStyle(stylex); cell3.setCellStyle(stylex); cell4.setCellStyle(stylex); //cell5.setCellStyle(stylex); //cell6.setCellStyle(stylex); //cell7.setCellStyle(stylex); //cell8.setCellStyle(stylex); //cell9.setCellStyle(stylex); //cell10.setCellStyle(stylex); //cell11.setCellStyle(stylex); //cell12.setCellStyle(stylex); cell13.setCellStyle(stylex); cell14.setCellStyle(stylex); cell15.setCellStyle(stylex); cell16.setCellStyle(stylex); cell17.setCellStyle(stylex); cell18.setCellStyle(stylex); cell19.setCellStyle(stylex); cell20.setCellStyle(stylex); cell21.setCellStyle(stylex); cell22.setCellStyle(stylex); cell23.setCellStyle(stylex); cell24.setCellStyle(stylex); cell25.setCellStyle(stylex); i = 1; previousDistrict = currentDistrict = ""; current_group = ""; String getClients = "SELECT DISTINCT(personal_information.client_id),personal_information.fname,personal_information.mname,personal_information.lname," + "DATE_FORMAT( NOW( ) , '%Y' ) - DATE_FORMAT( personal_information.dob, '%Y' )-( DATE_FORMAT( NOW( ),'YYYY-%mm-%dd' )< DATE_FORMAT( personal_information.dob, 'YYYY-%mm-%dd' ) )" + ",personal_information.gender,groups.group_name,district.district_name,partner.partner_name," + "service_provider.fname,service_provider.mname,service_provider.lname,personal_information.lessons_attended," + "personal_information.national_id,personal_information.ccc_no,personal_information.mobile_no,health_facility.hf_name,county.county_name," + "register2.session_no,SUM(register2.value) " + " FROM personal_information" + " LEFT JOIN health_facility ON personal_information.hf_id=health_facility.hf_id " + "LEFT JOIN district ON personal_information.district_id=district.district_id " + "LEFT JOIN county ON district.county_id=county.county_id " + "LEFT JOIN service_provider ON personal_information.provider_id=service_provider.provider_id " + "LEFT JOIN partner ON personal_information.partner_id=partner.partner_id " + "LEFT JOIN groups ON personal_information.group_id =groups.group_id " + "LEFT JOIN register2 ON personal_information.client_id=register2.client_id " + " WHERE register2.value<2 && STR_TO_DATE(register2.date,'%m/%d/%Y') BETWEEN STR_TO_DATE('" + startDate + "','%m/%d/%Y') AND STR_TO_DATE('" + endDate + "','%m/%d/%Y') " + " GROUP BY county.county_name,partner.partner_name,district.district_name,groups.group_name, register2.session_no ORDER BY partner.partner_name,district.district_name,groups.group_name"; conn.rs = conn.st.executeQuery(getClients); while (conn.rs.next()) { county = district = hf = partner = groupname = serviceprovider = clientname = age = gender = groupings = year = providerid = ""; countyid = districtid = hfid = partnerid = groupid = serviceproviderid = clientid = ""; s1 = s2 = s3 = s4 = s5 = s6 = s7 = s8 = s9 = s10 = s11 = s12 = s13 = ""; cm = rsp = cd = tb = sti = testedpartner = testedchild = session_no = value = status; sess = val = cds = 0; hf_id = lessons_attended = national_id = ccc_no = mobile_no = dob = ""; added = 0; message_no = achieved = 0; clientid = conn.rs.getString(1); if (!conn.rs.getString(3).equalsIgnoreCase(conn.rs.getString(4))) { clientname = conn.rs.getString(2) + " " + conn.rs.getString(3) + " " + conn.rs.getString(4); } else { clientname = conn.rs.getString(2) + " " + conn.rs.getString(4); } age = conn.rs.getString(5); gender = conn.rs.getString(6); groupname = conn.rs.getString(7); if (groupname == null) { groupname = "INDIVIDUAL"; } district = conn.rs.getString(8); partner = conn.rs.getString(9); serviceprovider = conn.rs.getString(10) + " " + conn.rs.getString(11) + " " + conn.rs.getString(12); if (conn.rs.getString(11) != null && conn.rs.getString(12) != null) { if (conn.rs.getString(11).equals(conn.rs.getString(12))) { serviceprovider = conn.rs.getString(10) + " " + conn.rs.getString(12); } } lessons_attended = conn.rs.getString(13); national_id = conn.rs.getString(14); ccc_no = conn.rs.getString(15); mobile_no = conn.rs.getString(16); hf = conn.rs.getString(17); county = conn.rs.getString(18); message_no = conn.rs.getInt(19); achieved = conn.rs.getInt(20); // OUTPUT ATTENDED-------------------------------- current_group = groupname; currentDistrict = district; if (current_group.equals("")) { previous_group = current_group; previousDistrict = currentDistrict; // OUTPUT SERVICES PROVIDED================================ HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; HSSFCell cell21x, cell22x, cell23x, cell24x, cell25x, cell26x, cell27x, cell28x, cell29x, cell30x, cell31x, cell32x, cell33x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // cell5x=rw4x.createCell(5); cell13x = rw4x.createCell(13); cell14x = rw4x.createCell(14); cell15x = rw4x.createCell(15); cell16x = rw4x.createCell(16); cell17x = rw4x.createCell(17); cell18x = rw4x.createCell(18); cell19x = rw4x.createCell(19); cell20x = rw4x.createCell(20); cell21x = rw4x.createCell(21); cell22x = rw4x.createCell(22); cell23x = rw4x.createCell(23); cell24x = rw4x.createCell(24); cell25x = rw4x.createCell(25); cell0x.setCellValue(county); cell1x.setCellValue(partner); cell2x.setCellValue(district); cell3x.setCellValue(hf); cell4x.setCellValue(groupname); // cell5x.setCellValue(achieved); if (message_no == 1) { cell13x.setCellValue(achieved); } if (message_no == 2) { cell14x.setCellValue(achieved); } if (message_no == 3) { cell15x.setCellValue(achieved); } if (message_no == 4) { cell16x.setCellValue(achieved); } if (message_no == 5) { cell17x.setCellValue(achieved); } if (message_no == 6) { cell18x.setCellValue(achieved); } if (message_no == 7) { cell19x.setCellValue(achieved); } if (message_no == 8) { cell20x.setCellValue(achieved); } if (message_no == 9) { cell21x.setCellValue(achieved); } if (message_no == 10) { cell22x.setCellValue(achieved); } if (message_no == 11) { cell23x.setCellValue(achieved); } if (message_no == 12) { cell24x.setCellValue(achieved); } if (message_no == 13) { cell25x.setCellValue(achieved); } cell0x.setCellStyle(styleBorder); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell3x.setCellStyle(styleBorder); cell4x.setCellStyle(styleBorder); //cell5x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); cell14x.setCellStyle(styleBorder); cell15x.setCellStyle(styleBorder); cell16x.setCellStyle(styleBorder); cell17x.setCellStyle(styleBorder); cell18x.setCellStyle(styleBorder); cell19x.setCellStyle(styleBorder); cell20x.setCellStyle(styleBorder); cell21x.setCellStyle(styleBorder); cell22x.setCellStyle(styleBorder); cell23x.setCellStyle(styleBorder); cell24x.setCellStyle(styleBorder); cell25x.setCellStyle(styleBorder); i++; } else if (!currentDistrict.equals(previousDistrict)) { previousDistrict = currentDistrict; previous_group = current_group; // OUTPUT SERVICES PROVIDED================================ HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; HSSFCell cell21x, cell22x, cell23x, cell24x, cell25x, cell26x, cell27x, cell28x, cell29x, cell30x, cell31x, cell32x, cell33x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // cell5x=rw4x.createCell(5); cell13x = rw4x.createCell(13); cell14x = rw4x.createCell(14); cell15x = rw4x.createCell(15); cell16x = rw4x.createCell(16); cell17x = rw4x.createCell(17); cell18x = rw4x.createCell(18); cell19x = rw4x.createCell(19); cell20x = rw4x.createCell(20); cell21x = rw4x.createCell(21); cell22x = rw4x.createCell(22); cell23x = rw4x.createCell(23); cell24x = rw4x.createCell(24); cell25x = rw4x.createCell(25); cell0x.setCellValue(county); cell1x.setCellValue(partner); cell2x.setCellValue(district); cell3x.setCellValue(hf); cell4x.setCellValue(groupname); // cell5x.setCellValue(achieved); if (message_no == 1) { cell13x.setCellValue(achieved); } if (message_no == 2) { cell14x.setCellValue(achieved); } if (message_no == 3) { cell15x.setCellValue(achieved); } if (message_no == 4) { cell16x.setCellValue(achieved); } if (message_no == 5) { cell17x.setCellValue(achieved); } if (message_no == 6) { cell18x.setCellValue(achieved); } if (message_no == 7) { cell19x.setCellValue(achieved); } if (message_no == 8) { cell20x.setCellValue(achieved); } if (message_no == 9) { cell21x.setCellValue(achieved); } if (message_no == 10) { cell22x.setCellValue(achieved); } if (message_no == 11) { cell23x.setCellValue(achieved); } if (message_no == 12) { cell24x.setCellValue(achieved); } if (message_no == 13) { cell25x.setCellValue(achieved); } cell0x.setCellStyle(styleBorder); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell3x.setCellStyle(styleBorder); cell4x.setCellStyle(styleBorder); //cell5x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); cell14x.setCellStyle(styleBorder); cell15x.setCellStyle(styleBorder); cell16x.setCellStyle(styleBorder); cell17x.setCellStyle(styleBorder); cell18x.setCellStyle(styleBorder); cell19x.setCellStyle(styleBorder); cell20x.setCellStyle(styleBorder); cell21x.setCellStyle(styleBorder); cell22x.setCellStyle(styleBorder); cell23x.setCellStyle(styleBorder); cell24x.setCellStyle(styleBorder); cell25x.setCellStyle(styleBorder); i++; } else if (!current_group.equals(previous_group)) { // update attended sessions; previous_group = current_group; // OUTPUT SERVICES PROVIDED================================ HSSFRow rw4x = shet1.createRow(i); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell0x, cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x, cell11x, cell12x, cell13x, cell14x, cell15x, cell16x, cell17x, cell18x, cell19x, cell20x; HSSFCell cell21x, cell22x, cell23x, cell24x, cell25x, cell26x, cell27x, cell28x, cell29x, cell30x, cell31x, cell32x, cell33x; cell0x = rw4x.createCell(0); cell1x = rw4x.createCell(1); cell2x = rw4x.createCell(2); cell3x = rw4x.createCell(3); cell4x = rw4x.createCell(4); // cell5x=rw4x.createCell(5); cell13x = rw4x.createCell(13); cell14x = rw4x.createCell(14); cell15x = rw4x.createCell(15); cell16x = rw4x.createCell(16); cell17x = rw4x.createCell(17); cell18x = rw4x.createCell(18); cell19x = rw4x.createCell(19); cell20x = rw4x.createCell(20); cell21x = rw4x.createCell(21); cell22x = rw4x.createCell(22); cell23x = rw4x.createCell(23); cell24x = rw4x.createCell(24); cell25x = rw4x.createCell(25); cell0x.setCellValue(county); cell1x.setCellValue(partner); cell2x.setCellValue(district); cell3x.setCellValue(hf); cell4x.setCellValue(groupname); // cell5x.setCellValue(achieved); if (message_no == 1) { cell13x.setCellValue(achieved); } if (message_no == 2) { cell14x.setCellValue(achieved); } if (message_no == 3) { cell15x.setCellValue(achieved); } if (message_no == 4) { cell16x.setCellValue(achieved); } if (message_no == 5) { cell17x.setCellValue(achieved); } if (message_no == 6) { cell18x.setCellValue(achieved); } if (message_no == 7) { cell19x.setCellValue(achieved); } if (message_no == 8) { cell20x.setCellValue(achieved); } if (message_no == 9) { cell21x.setCellValue(achieved); } if (message_no == 10) { cell22x.setCellValue(achieved); } if (message_no == 11) { cell23x.setCellValue(achieved); } if (message_no == 12) { cell24x.setCellValue(achieved); } if (message_no == 13) { cell25x.setCellValue(achieved); } cell0x.setCellStyle(styleBorder); cell1x.setCellStyle(styleBorder); cell2x.setCellStyle(styleBorder); cell3x.setCellStyle(styleBorder); cell4x.setCellStyle(styleBorder); //cell5x.setCellStyle(styleBorder); cell13x.setCellStyle(styleBorder); cell14x.setCellStyle(styleBorder); cell15x.setCellStyle(styleBorder); cell16x.setCellStyle(styleBorder); cell17x.setCellStyle(styleBorder); cell18x.setCellStyle(styleBorder); cell19x.setCellStyle(styleBorder); cell20x.setCellStyle(styleBorder); cell21x.setCellStyle(styleBorder); cell22x.setCellStyle(styleBorder); cell23x.setCellStyle(styleBorder); cell24x.setCellStyle(styleBorder); cell25x.setCellStyle(styleBorder); i++; } else if (current_group.equals(previous_group) && currentDistrict.equals(previousDistrict)) { HSSFRow rw4x = shet1.getRow(i - 1); if (message_no == 1) { HSSFCell cell13x = rw4x.createCell(13); cell13x.setCellValue(achieved); cell13x.setCellStyle(styleBorder); } if (message_no == 2) { HSSFCell cell14x = rw4x.createCell(14); cell14x.setCellValue(achieved); cell14x.setCellStyle(styleBorder); } if (message_no == 3) { HSSFCell cell15x = rw4x.createCell(15); cell15x.setCellValue(achieved); cell15x.setCellStyle(styleBorder); } if (message_no == 4) { HSSFCell cell16x = rw4x.createCell(16); cell16x.setCellValue(achieved); cell16x.setCellStyle(styleBorder); } if (message_no == 5) { HSSFCell cell17x = rw4x.createCell(17); cell17x.setCellValue(achieved); cell17x.setCellStyle(styleBorder); } if (message_no == 6) { HSSFCell cell18x = rw4x.createCell(18); cell18x.setCellValue(achieved); cell18x.setCellStyle(styleBorder); } if (message_no == 7) { HSSFCell cell19x = rw4x.createCell(19); cell19x.setCellValue(achieved); cell19x.setCellStyle(styleBorder); } if (message_no == 8) { HSSFCell cell20x = rw4x.createCell(20); cell20x.setCellValue(achieved); cell20x.setCellStyle(styleBorder); } if (message_no == 9) { HSSFCell cell21x = rw4x.createCell(21); cell21x.setCellValue(achieved); cell21x.setCellStyle(styleBorder); } if (message_no == 10) { HSSFCell cell22x = rw4x.createCell(22); cell22x.setCellValue(achieved); cell22x.setCellStyle(styleBorder); } if (message_no == 11) { HSSFCell cell23x = rw4x.createCell(23); cell23x.setCellValue(achieved); cell23x.setCellStyle(styleBorder); } if (message_no == 12) { HSSFCell cell24x = rw4x.createCell(24); cell24x.setCellValue(achieved); cell24x.setCellStyle(styleBorder); } if (message_no == 13) { HSSFCell cell25x = rw4x.createCell(25); cell25x.setCellValue(achieved); cell25x.setCellStyle(styleBorder); } } else { System.out.println("here-------------nothing seen"); } } System.out.println("here : " + i); if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_Raw_Data.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.reportsTracker731.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { dbConn conn = new dbConn(); session = request.getSession();//ww w . ja v a2s . c o m year = Integer.parseInt(request.getParameter("year")); // year=2015; prevYear = year - 1; IdGenerator IG = new IdGenerator(); allMonths.clear(); allReports.clear(); duration = "WHERE (moh731.yearmonth BETWEEN " + prevYear + "" + 10 + " AND " + year + "09) AND (subpartnera.PMTCT=1 OR subpartnera.ART=1 OR subpartnera.PEP=1 OR subpartnera.HTC=1)"; currentMonth = IG.CurrentMonth(); monthsData = ""; // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet("MOH 731 REPORTS TRACKER"); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); stborder.setWrapText(true); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); HSSFCellStyle styleHeader = wb.createCellStyle(); styleHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontHeader = wb.createFont(); fontHeader.setColor(HSSFColor.DARK_BLUE.index); styleHeader.setFont(fontHeader); styleHeader.setWrapText(true); for (int i = 0; i <= 2; i++) { shet1.setColumnWidth(i, 8000); } HSSFRow rw1S1 = shet1.createRow(0); HSSFCell S1cell = rw1S1.createCell(0); S1cell.setCellValue("COUNTY NAME"); S1cell.setCellStyle(stylex); HSSFCell S1cellX = rw1S1.createCell(1); S1cellX.setCellValue("SUB COUNTY"); S1cellX.setCellStyle(stylex); S1cellX = rw1S1.createCell(2); S1cellX.setCellValue("HEALTH FACILITY"); S1cellX.setCellStyle(stylex); S1cellX = rw1S1.createCell(3); S1cellX.setCellValue("MFL CODE"); S1cellX.setCellStyle(stylex); S1cellX = rw1S1.createCell(4); S1cellX.setCellValue("EXPECTED REPORTS"); S1cellX.setCellStyle(stylex); counterHeader = 5; String getMaxandMin = "SELECT DISTINCT(month.name),month.id FROM moh731 JOIN month ON moh731.Mois=month.id JOIN subpartnera ON moh731.SubPartnerID=subpartnera.SubPartnerID " + duration + " " + " ORDER BY moh731.yearmonth"; conn.rs = conn.st.executeQuery(getMaxandMin); while (conn.rs.next()) { monthName = conn.rs.getString(1); monthid = conn.rs.getInt(2); if (monthid <= 9) { currentYear = year; } else { currentYear = prevYear; } System.out.println(" Months are : " + monthName); allMonths.add(monthName); allReports.add(0); S1cellX = rw1S1.createCell(counterHeader); S1cellX.setCellValue(monthName); S1cellX.setCellStyle(stylex); counterHeader++; } prevFacility = currentFacility = ""; currentDistrict = prevDistrict = ""; currentCounty = prevCounty = ""; counter = districtCounter = countyCounter = districtsMerged = 0; arraySize = allReports.size(); if (allMonths.size() > 0) { String checkReports = "SELECT county.County,district.DistrictNom,subpartnera.SubPartnerNom," + "subpartnera.CentreSanteId,COUNT(moh731.SubPartnerID),month.name,subpartnera.SubPartnerID " + "FROM subpartnera " + "LEFT JOIN moh731 ON moh731.SubPartnerID=subpartnera.SubPartnerID " + "LEFT JOIN district ON subpartnera.DistrictID=district.DistrictID " + "LEFT JOIN county ON county.CountyID=district.CountyID " + "LEFT JOIN month ON moh731.Mois=month.id " + " " + duration + " " + " GROUP BY subpartnera.SubPartnerNom,moh731.Annee,moh731.Mois " + "ORDER BY county.County,district.DistrictNom,subpartnera.SubPartnerNom,moh731.Mois"; System.out.println(checkReports); conn.rs = conn.st.executeQuery(checkReports); while (conn.rs.next()) { countyName = conn.rs.getString(1); districtName = conn.rs.getString(2); facilityName = conn.rs.getString(3); mflcode = conn.rs.getString(4); status = conn.rs.getInt(5); selectedMonth = conn.rs.getString(6); currentFacility = conn.rs.getString(7); currentDistrict = districtName; currentCounty = countyName; // CHECK WHERE TO PLACE THE NUMBER; monthPosition = allMonths.indexOf(selectedMonth); if (!prevFacility.equals(currentFacility)) { if (!prevDistrict.equals(currentDistrict) && !prevDistrict.equals("")) { counter++; noReports = districtCounter + 1; HSSFRow rwTotal = shet1.createRow(counter); HSSFCell SX = rwTotal.createCell(0); SX.setCellStyle(stborder); SX = rwTotal.createCell(1); SX.setCellValue(prevDistrict + " TOTALS : "); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(2); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(3); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(4); SX.setCellValue(noReports); SX.setCellStyle(styleHeader); shet1.addMergedRegion(new CellRangeAddress(counter, counter, 1, 3)); for (int j = 0; j < allReports.size(); j++) { // System.out.println("district name : "+prevDistrict+" no of reports : "+allReports.get(j).toString()+" for month : "+allMonths.get(j).toString()); int dataPos = 5 + j; SX = rwTotal.createCell(dataPos); SX.setCellValue(Integer.parseInt(allReports.get(j).toString())); SX.setCellStyle(styleHeader); } for (int k = 0; k < arraySize; k++) { allReports.set(k, 0); } } counter++; HSSFRow rw1 = shet1.createRow(counter); HSSFCell S1 = rw1.createCell(0); S1.setCellValue(countyName); S1.setCellStyle(stborder); S1 = rw1.createCell(1); S1.setCellValue(districtName); S1.setCellStyle(stborder); S1 = rw1.createCell(2); S1.setCellValue(facilityName); S1.setCellStyle(stborder); S1 = rw1.createCell(3); S1.setCellValue(mflcode); S1.setCellStyle(stborder); S1 = rw1.createCell(4); S1.setCellValue(1); S1.setCellStyle(stborder); for (int j = 0; j < allMonths.size(); j++) { int cellPos = j + 5; S1 = rw1.createCell(cellPos); // System.out.println("counter : "+counter+" datapos : "+cellPos+" status : "+status); S1.setCellStyle(stborder); } int dataPos = 5 + monthPosition; S1 = rw1.getCell(dataPos); S1.setCellValue(status); if (!prevDistrict.equals(currentDistrict) && !prevDistrict.equals("")) { int distStart = counter - districtCounter - 2; int distEnd = counter - 2; shet1.addMergedRegion(new CellRangeAddress(distStart, distEnd, 1, 1)); districtsMerged++; districtCounter = 0; for (int j = 0; j < arraySize; j++) { allReports.set(j, 0); } } else { if (counter == 1) { } else { districtCounter++; } } if (!prevCounty.equals(currentCounty) && !prevCounty.equals("")) { int countyStart = counter - countyCounter - districtsMerged - 1; int countyEnd = counter - 1; shet1.addMergedRegion(new CellRangeAddress(countyStart, countyEnd, 0, 0)); countyCounter = 0; districtsMerged = 0; } else { if (counter == 1) { } else { countyCounter++; } } prevCounty = currentCounty; prevDistrict = currentDistrict; } else { HSSFRow rw1 = shet1.getRow(counter); int dataPos = 5 + monthPosition; HSSFCell S1 = rw1.getCell(dataPos); S1.setCellValue(status); // } if (status == 1) { int currentData = Integer.parseInt(allReports.get(monthPosition).toString()) + 1; allReports.set(monthPosition, currentData); } prevFacility = currentFacility; } // MATCH THE LAST DISTRICTS counter++; noReports = districtCounter + 1; HSSFRow rwTotal = shet1.createRow(counter); HSSFCell SX = rwTotal.createCell(0); SX.setCellStyle(stborder); SX = rwTotal.createCell(1); SX.setCellValue(prevDistrict + " TOTALS : "); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(2); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(3); SX.setCellStyle(styleHeader); SX = rwTotal.createCell(4); SX.setCellValue(noReports); SX.setCellStyle(styleHeader); shet1.addMergedRegion(new CellRangeAddress(counter, counter, 1, 3)); int distStart = counter - districtCounter - 1; int distEnd = counter - 1; // System.out.println("MERGE BETWEEN : START : "+distStart+" END : "+distEnd); shet1.addMergedRegion(new CellRangeAddress(distStart, distEnd, 1, 1)); districtCounter = 0; int countyStart = counter - countyCounter - 2; int countyEnd = counter; // System.out.println("MERGE BETWEEN : START : "+distStart+" END : "+distEnd); shet1.addMergedRegion(new CellRangeAddress(countyStart, countyEnd, 0, 0)); countyCounter = 0; for (int j = 0; j < allReports.size(); j++) { // System.out.println("district name : "+prevDistrict+" no of reports : "+allReports.get(j).toString()+" for month : "+allMonths.get(j).toString()); int dataPos = 5 + j; SX = rwTotal.createCell(dataPos); SX.setCellValue(Integer.parseInt(allReports.get(j).toString())); SX.setCellStyle(styleHeader); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.conn != null) { conn.conn.close(); } createdOn = IG.CreatedOn(); // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=Reporting_summary_for_PEPFAR_YEAR(" + year + ")_" + createdOn.trim() + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } else { session.setAttribute("noTrackerReport", "<font color=\"red\"><b>SORRY:</b> No report was found for " + year + ".</red>"); response.sendRedirect("reportsTracker.jsp"); } }
From source file:reports.resultspercbo.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods./*from w w w . j a va 2 s . c om*/ * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { dbConn conn = new dbConn(); wb = new HSSFWorkbook(); HSSFSheet shet2 = null; String year = ""; String site = ""; String period = ""; String cbo = ""; String startdate = "2015-01-01"; String enddate = "2015-03-30"; startdate = request.getParameter("startdate"); enddate = request.getParameter("enddate"); // year=request.getParameter("year"); //site=request.getParameter("sitecbo"); //period=request.getParameter("period"); //cbo=request.getParameter("staffcbo"); String sitename = ""; String cboname = ""; //begin a loop that will create as many reports as possible HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName("Cambria"); // font.setItalic(true); font.setBoldweight((short) 02); font.setColor(HSSFColor.BLACK.index); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setWrapText(true); style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); style.setAlignment(style.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //%%%%%%%%%%%%%%%%HEADER FONTS AND COLORATION%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% HSSFFont font_header = wb.createFont(); font_header.setFontHeightInPoints((short) 10); font_header.setFontName("Eras Bold ITC"); // font.setItalic(true); font_header.setBoldweight((short) 05); font_header.setColor(HSSFColor.BLACK.index); CellStyle style_header = wb.createCellStyle(); style_header.setFont(font_header); style_header.setWrapText(true); style_header.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); style_header.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style_header.setAlignment(style_header.ALIGN_CENTER); // style_header.setBorderBottom(HSSFCellStyle.BORDER_THIN); // style_header.setBorderTop(HSSFCellStyle.BORDER_THIN); // style_header.setBorderRight(HSSFCellStyle.BORDER_THIN); // style_header.setBorderLeft(HSSFCellStyle.BORDER_THIN); //%%%%%%%%%%%%%%%%%%%%%%%%%DATA FONT%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% //font data HSSFFont datafont = wb.createFont(); datafont.setBoldweight((short) 03); datafont.setColor(HSSFColor.BLACK.index); datafont.setFontHeightInPoints((short) 10); datafont.setFontName("Cambria"); datafont.setItalic(true); //bold font HSSFFont bolfont = wb.createFont(); bolfont.setBoldweight((short) 05); bolfont.setColor(HSSFColor.BLACK.index); bolfont.setFontHeightInPoints((short) 12); bolfont.setFontName("Cambria"); //=========================ROW STYLE=============================== HSSFCellStyle rowstyle = wb.createCellStyle(); rowstyle.setWrapText(true); //=======INNER DATA STYLING=========================== CellStyle innerdata_style = wb.createCellStyle(); innerdata_style.setFont(datafont); innerdata_style.setWrapText(true); innerdata_style.setAlignment(innerdata_style.ALIGN_CENTER); innerdata_style.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle lastcellrighborder = wb.createCellStyle(); lastcellrighborder.setFont(datafont); lastcellrighborder.setWrapText(true); lastcellrighborder.setAlignment(lastcellrighborder.ALIGN_CENTER); lastcellrighborder.setFillForegroundColor(HSSFColor.WHITE.index); lastcellrighborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // lastcellrighborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderTop(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // lastcellrighborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); lastcellrighborder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle innerdata_style2 = wb.createCellStyle(); innerdata_style2.setFont(bolfont); innerdata_style2.setWrapText(true); innerdata_style2.setAlignment(innerdata_style.ALIGN_LEFT); innerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); innerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); innerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle orangestyle = wb.createCellStyle(); orangestyle.setFont(bolfont); orangestyle.setWrapText(true); orangestyle.setAlignment(orangestyle.ALIGN_CENTER); orangestyle.setFillForegroundColor(HSSFColor.ORANGE.index); orangestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // innerdata_style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderTop(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderRight(HSSFCellStyle.BORDER_THIN); // innerdata_style.setBorderLeft(HSSFCellStyle.BORDER_THIN); orangestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //Code colors CellStyle lg = wb.createCellStyle(); lg.setFont(bolfont); lg.setWrapText(true); lg.setAlignment(lg.ALIGN_CENTER); lg.setFillForegroundColor(HSSFColor.GREEN.index); lg.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); lg.setBorderBottom(HSSFCellStyle.BORDER_THIN); lg.setBorderTop(HSSFCellStyle.BORDER_THIN); lg.setBorderRight(HSSFCellStyle.BORDER_THIN); lg.setBorderLeft(HSSFCellStyle.BORDER_THIN); lg.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle Y = wb.createCellStyle(); Y.setFont(bolfont); Y.setWrapText(true); Y.setAlignment(Y.ALIGN_CENTER); Y.setFillForegroundColor(HSSFColor.WHITE.index); Y.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); Y.setBorderBottom(HSSFCellStyle.BORDER_THIN); Y.setBorderTop(HSSFCellStyle.BORDER_THIN); Y.setBorderRight(HSSFCellStyle.BORDER_THIN); Y.setBorderLeft(HSSFCellStyle.BORDER_THIN); Y.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); CellStyle R = wb.createCellStyle(); R.setFont(bolfont); R.setWrapText(true); R.setAlignment(R.ALIGN_CENTER); R.setFillForegroundColor(HSSFColor.RED.index); R.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); R.setBorderBottom(HSSFCellStyle.BORDER_THIN); R.setBorderTop(HSSFCellStyle.BORDER_THIN); R.setBorderRight(HSSFCellStyle.BORDER_THIN); R.setBorderLeft(HSSFCellStyle.BORDER_THIN); R.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //=======INNER LEFT DATA STYLING=========================== CellStyle binnerdata_style2 = wb.createCellStyle(); binnerdata_style2.setFont(datafont); binnerdata_style2.setWrapText(true); binnerdata_style2.setAlignment(binnerdata_style2.ALIGN_LEFT); binnerdata_style2.setFillForegroundColor(HSSFColor.WHITE.index); binnerdata_style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); binnerdata_style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderTop(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderRight(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); binnerdata_style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //create a header //=======================Domainname styles CellStyle dnamestyle = wb.createCellStyle(); dnamestyle.setFont(bolfont); dnamestyle.setWrapText(true); dnamestyle.setAlignment(dnamestyle.ALIGN_LEFT); dnamestyle.setFillForegroundColor(HSSFColor.WHITE.index); dnamestyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); dnamestyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderTop(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderRight(HSSFCellStyle.BORDER_THIN); dnamestyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); dnamestyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); shet2 = wb.createSheet("LIP_REPORT"); shet2.setColumnWidth(0, 8000); shet2.setColumnWidth(1, 5000); shet2.setColumnWidth(2, 5000); shet2.setColumnWidth(3, 5000); shet2.setColumnWidth(4, 5000); shet2.setColumnWidth(5, 5000); shet2.setColumnWidth(6, 5000); shet2.setColumnWidth(7, 5000); shet2.setColumnWidth(8, 5000); shet2.setColumnWidth(9, 5000); shet2.setColumnWidth(10, 5000); shet2.setColumnWidth(11, 5000); shet2.setColumnWidth(12, 5000); shet2.setColumnWidth(13, 5000); //create header one HSSFRow header = shet2.createRow(0); header.setHeightInPoints(30); HSSFCell cel1 = header.createCell(0); cel1.setCellValue("Results on LIP Initial conducted from " + startdate + " to " + enddate); cel1.setCellStyle(style); for (int b = 1; b < 14; b++) { cel1 = header.createCell(b); cel1.setCellValue(""); cel1.setCellStyle(style); } shet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 13)); //create header two HSSFRow header2 = shet2.createRow(1); header2.setHeightInPoints(28); HSSFCell cel2 = null; for (int b = 1; b < 14; b++) { cel2 = header2.createCell(b); cel2.setCellValue(""); cel2.setCellStyle(style); } cel2 = header2.createCell(0); cel2.setCellValue("Percent Scores Per Domain"); cel2.setCellStyle(style); shet2.addMergedRegion(new CellRangeAddress(1, 1, 0, 13)); //cbo name //create header three //==========DATE OF VISIT String mywhere = " ass_date between '" + startdate + "' and '" + enddate + "' "; //if the current countyid is 0, then the where code should not specify the county name //=============================================================================================== //=============================================================================================== String getdomains = "SELECT domain_id,domain_name,section_name FROM domains join sections on domains.section_id=sections.section_id"; conn.rs = conn.st.executeQuery(getdomains); ArrayList domainids = new ArrayList(); int r = 2; HSSFRow theader = shet2.createRow(r); HSSFRow domainshd = shet2.createRow(r + 1); //create a row with title lip HSSFCell tce = domainshd.createCell(0); tce.setCellValue("LIP"); tce.setCellStyle(Y); int cnt = 1; while (conn.rs.next()) { HSSFCell tcel = theader.createCell(cnt); tcel.setCellValue(conn.rs.getString("section_name")); tcel.setCellStyle(Y); theader.setHeightInPoints(24); HSSFCell tcel1 = domainshd.createCell(cnt); tcel1.setCellValue(conn.rs.getString("domain_name")); tcel1.setCellStyle(Y); domainids.add(conn.rs.getString("domain_id")); cnt++; } //create avarage header HSSFCell tcel = domainshd.createCell(cnt); tcel.setCellValue("Avarage"); tcel.setCellStyle(Y); shet2.addMergedRegion(new CellRangeAddress(r, r, 1, 5)); shet2.addMergedRegion(new CellRangeAddress(r, r, 6, 12)); cnt++; // shet2.addMergedRegion(new CellRangeAddress(7,7,0,5)); // String gettables = "SELECT domain_name,domains.domain_id as domainid,section_name,domains.section_id as secid,value as domainvalue,aggregate_sum,period,year,site FROM domains join sections on domains.section_id=sections.section_id join domain_totals on domains.domain_id=domain_totals.domainid where "+mywhere+" order by domainid"; String gettables = "SELECT avg(value) as domainvalue,domain_totals.domainid as domainid,cbo,avg(aggregate_sum) as aggregate_sum FROM ovc_lip.domain_totals join (sites join cbo on sites.cbo_id=cbo.cboid) on domain_totals.site=sites.site_id where date between '" + startdate + "' and '" + enddate + "' group by cbo.cboid,domainid order by cbo,domainid"; //if its the first county, themn skip the county part System.out.println(gettables); conn.rs = conn.st.executeQuery(gettables); int rwcount = 4; HSSFRow rwx = null; HSSFCell celx = null; String sectioncopy = ""; int rowcopy = 8; while (conn.rs.next()) { //if the section has changed String domainid = conn.rs.getString("domainid"); float domainvalue = conn.rs.getFloat("domainvalue"); domainvalue = domainvalue * 100; //BigDecimal bd=new BigDecimal(domainvalue).setScale(0,RoundingMode.HALF_EVEN); //domainval=bd.doubleValue(); domainvalue = Math.round(domainvalue); float totalsum = conn.rs.getFloat("aggregate_sum"); // totalsum=totalsum*100; int dmn = (int) domainvalue; totalsum = Math.round(totalsum); //determine the cell to print data on int ttlsm = (int) totalsum; if (domainid.equals("1")) { rwx = shet2.createRow(rwcount); rwx.setHeightInPoints(22); HSSFCell celx2 = rwx.createCell(0); celx2.setCellValue("" + conn.rs.getString("cbo")); celx2.setCellStyle(dnamestyle); rwcount++; } for (int t = 0; t < domainids.size(); t++) { //if row is blank create it if (rwx == null) { rwx = shet2.createRow(rwcount); } if (domainids.get(t).equals(domainid)) { int ct = t + 1; HSSFCell celx1 = rwx.createCell(ct); celx1.setCellValue("" + dmn); celx1.setCellStyle(dnamestyle); rwx.setHeightInPoints(22); //System.out.println("worked in row ============="+rwcount+"__col "+(ct)+"_"+domainvalue); } } //incement if the column is the last if (domainid.equals("12")) { //create an avarage // System.out.println("XXXXX LAST LOOP"); if (rwx == null) { rwx = shet2.createRow(rwcount); } celx = rwx.createCell(13); rwx.setHeightInPoints(23); celx.setCellValue("" + ttlsm); celx.setCellStyle(dnamestyle); // rwcount++; } } ///=========================end of while loop //write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=OVC_LIP_COUNTY_REPORT_" + startdate + "_" + enddate + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); } catch (SQLException ex) { Logger.getLogger(resultspercbo.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:reports.servicesProvided.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();//from w ww.jav a 2s .c om dbConn conn = new dbConn(); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); // font.setItalic(true); // font.setBoldweight((short)12); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); // font2.setFontHeightInPoints((short)15); font2.setFontName("Arial Black"); // font.setItalic(true); // font2.setBoldweight((short)18); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // stborder.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 6000); shet1.setColumnWidth(1, 6000); shet1.setColumnWidth(2, 6000); shet1.setColumnWidth(3, 6000); shet1.setColumnWidth(4, 8000); // shet1.setColumnWidth(20, 2000); HSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; cell = rw1.createCell(0); cell.setCellValue("PWP SERVICES PROVIDED PER MONTH "); cell.setCellStyle(style); rw1.setHeightInPoints(30); // Merge the cells shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); // CREATE HEADING 2 HSSFRow rheading2 = shet1.createRow(2); rheading2.setHeightInPoints(25); HSSFCell cellxx1, cellxx2, cellxx3, cellxx4, cellxx5, cellxx6; cellxx1 = rheading2.createCell(0); cellxx2 = rheading2.createCell(1); cellxx3 = rheading2.createCell(2); cellxx4 = rheading2.createCell(3); cellxx5 = rheading2.createCell(4); // cellxx6=rheading2.createCell(5); cellxx1.setCellValue("COUNTY NAME"); cellxx2.setCellValue("PARTNER NAME"); cellxx3.setCellValue("YEAR"); cellxx4.setCellValue("MONTH"); cellxx5.setCellValue("NO. OF CLIENTS GIVEN SERVICES"); cellxx1.setCellStyle(styleBorder); cellxx2.setCellStyle(styleBorder); cellxx3.setCellStyle(styleBorder); cellxx4.setCellStyle(styleBorder); cellxx5.setCellStyle(styleBorder); pos = 3; HSSFCellStyle stylex = wb.createCellStyle(); //stylex.setFillForegroundColor(HSSFColor.LIME.index); //stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); String getData = "SELECT county.county_name AS COUNTY,partner.partner_name AS PARTNER, " + "COUNT(DISTINCT services_provided.client_id) AS ACHIEVED," + "services_provided.submission_year,services_provided.submission_month," + " CASE" + " WHEN max(services_provided.submission_month) BETWEEN 01 AND 03 THEN 'Q2' " + " WHEN max(services_provided.submission_month) BETWEEN 04 AND 06 THEN 'Q3' " + " WHEN max(services_provided.submission_month) BETWEEN 07 AND 09 THEN 'Q4' " + " WHEN max(services_provided.submission_month) BETWEEN 10 AND 12 THEN 'Q1' " + " ELSE 'NOT SELECTED' " + " END AS QUARTER, " + "CASE" + " when services_provided.submission_month =01 THEN 'JAN'" + " when services_provided.submission_month =02 THEN 'FEB'" + " when services_provided.submission_month =03 THEN 'MAR'" + " when services_provided.submission_month=04 THEN 'APR'" + " when services_provided.submission_month=05 THEN 'MAY'" + " when services_provided.submission_month=06 THEN 'JUN'" + " when services_provided.submission_month=07 THEN 'JUL'" + " when services_provided.submission_month=08 THEN 'AUG'" + " when services_provided.submission_month=09 THEN 'SEPT'" + " when services_provided.submission_month=10 THEN 'OCT'" + " when services_provided.submission_month=11 THEN 'NOV'" + " when services_provided.submission_month=12 THEn 'DEC'" + " END AS MONTHS " + "FROM personal_information JOIN services_provided ON personal_information.client_id=services_provided.client_id " + "JOIN partner ON partner.partner_id=personal_information.partner_id JOIN (district JOIN county ON county.county_id=district.county_id) " + "ON district.district_id=personal_information.district_id " + "WHERE contraceptive_method='YES' OR rsp='YES' OR cds_given>0 OR screened_tb='YES' OR screened_stis='YES' " + "OR tested_partner='YES' OR tested_children='YES' OR disclosed_status='YES' " + "GROUP BY county.county_name,partner.partner_name,services_provided.submission_year,services_provided.submission_month"; conn.rs = conn.st.executeQuery(getData); while (conn.rs.next()) { countyname = conn.rs.getString(1); partnername = conn.rs.getString(2); achieved = conn.rs.getInt(3); month = conn.rs.getString(7); year = conn.rs.getInt(4); // CREATE ROW AND ADD DATA TO THE DATA CELLS====================== HSSFRow data = shet1.createRow(pos); data.setHeightInPoints(25); HSSFCell cellx1, cellx2, cellx3, cellx4, cellx5, cellx6; cellx1 = data.createCell(0); cellx2 = data.createCell(1); cellx3 = data.createCell(2); cellx4 = data.createCell(3); cellx5 = data.createCell(4); // cellxx6=rheading2.createCell(5); cellx1.setCellValue(countyname); cellx2.setCellValue(partnername); cellx3.setCellValue(year); cellx4.setCellValue(month); cellx5.setCellValue(achieved); cellx1.setCellStyle(stylex); cellx2.setCellStyle(stylex); cellx3.setCellStyle(stylex); cellx4.setCellStyle(stylex); cellx5.setCellStyle(stylex); System.out.println("county : " + countyname + " partner : " + partnername + " ahieved:" + achieved + " month: " + month + " quarter: " + quarter); pos++; } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_SERVICES_PROVIDED_REPORT.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.sessionsBetween.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { dbConn conn = new dbConn(); session = request.getSession();/*from ww w .java 2 s .c om*/ String[] starter = request.getParameter("start_date").split("/"); String[] ender = request.getParameter("end_date").split("/"); // String [] starter="10/02/2010".split("/"); // String [] ender="24/10/2014".split("/"); String m1 = "", m2 = ""; String d1 = "", d2 = "", y1 = "", y2 = ""; start = request.getParameter("start_date"); end = request.getParameter("end_date"); m1 = starter[1]; m2 = ender[1]; d1 = starter[0]; d2 = ender[0]; y1 = starter[2]; y2 = ender[2]; start_dateKey = m1 + "/" + d1 + "/" + y1; end_dateKey = m2 + "/" + d2 + "/" + y2; // System.out.println(""); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet(); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); // font.setItalic(true); // font.setBoldweight((short)12); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); // font2.setFontHeightInPoints((short)15); font2.setFontName("Arial Black"); // font.setItalic(true); // font2.setBoldweight((short)18); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); // stborder.setFillForegroundColor(HSSFColor.ORANGE.index); // stborder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // HSSFSheet sheet1 = wb.getSheetAt(0); shet1.setColumnWidth(0, 4000); shet1.setColumnWidth(1, 4000); shet1.setColumnWidth(2, 4000); shet1.setColumnWidth(3, 4500); shet1.setColumnWidth(4, 4300); shet1.setColumnWidth(5, 4300); shet1.setColumnWidth(6, 4000); shet1.setColumnWidth(7, 4200); shet1.setColumnWidth(8, 4200); shet1.setColumnWidth(9, 4200); HSSFCellStyle styleBorder = wb.createCellStyle(); styleBorder.setBorderTop(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleBorder.setBorderRight(HSSFCellStyle.BORDER_THIN); styleBorder.setFillForegroundColor(HSSFColor.ORANGE.index); styleBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFRow rw1 = shet1.createRow(1); HSSFCell cell; cell = rw1.createCell(0); cell.setCellValue("PWP number of individual provided with services from " + start + " to " + end); cell.setCellStyle(style); rw1.setHeightInPoints(30); // Merge the cells shet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 9)); HSSFRow rw4 = shet1.createRow(2); rw4.setHeightInPoints(45); rw4.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10; cell1 = rw4.createCell(0); cell2 = rw4.createCell(1); cell3 = rw4.createCell(2); cell4 = rw4.createCell(3); cell5 = rw4.createCell(4); cell6 = rw4.createCell(5); cell7 = rw4.createCell(6); cell8 = rw4.createCell(7); cell9 = rw4.createCell(8); cell10 = rw4.createCell(9); cell1.setCellValue("County Name"); cell2.setCellValue("Partner Name"); cell3.setCellValue("Received Contraceptives"); cell4.setCellValue("Reffered To Service Point"); cell5.setCellValue("Given Condoms"); cell6.setCellValue("Screened For TB"); cell7.setCellValue("Screened For STIs"); cell8.setCellValue("Partner Tested"); cell9.setCellValue("Children Tested"); cell10.setCellValue("Disclosed Status"); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.LIME.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); cell1.setCellStyle(stylex); cell2.setCellStyle(stylex); cell3.setCellStyle(stylex); cell4.setCellStyle(stylex); cell5.setCellStyle(stylex); cell6.setCellStyle(stylex); cell7.setCellStyle(stylex); cell8.setCellStyle(stylex); cell9.setCellStyle(stylex); cell10.setCellStyle(stylex); pos = 3; String ct_selector = "SELECT * FROM county"; conn.rs3 = conn.st3.executeQuery(ct_selector); while (conn.rs3.next()) { county_name = conn.rs3.getString(2); county_id = conn.rs3.getString(1); String partner_name_selector = "SELECT * FROM partner"; conn.rs = conn.st.executeQuery(partner_name_selector); while (conn.rs.next()) { partner_name = conn.rs.getString(2); partner_id = conn.rs.getString(1); cm2 = rsp2 = tb2 = stis2 = partner2 = children2 = status2 = cds2 = 0; all_clients = 0; // pos++; String district_selector = "SELECT district_id FROM district WHERE county_id='" + county_id + "'"; conn.rs1 = conn.st1.executeQuery(district_selector); while (conn.rs1.next()) { String district_id = conn.rs1.getString(1); String getClients = "SELECT client_id FROM personal_information WHERE partner_id='" + partner_id + "' && district_id='" + district_id + "'"; conn.rs4 = conn.st4.executeQuery(getClients); while (conn.rs4.next()) { String client_id = conn.rs4.getString(1); cm1 = rsp1 = tb1 = stis1 = partner1 = children1 = status1 = cds1 = 0; String getServices = "SELECT * FROM services_provided WHERE client_id='" + client_id + "' && " + "STR_TO_DATE(submission_date,'%m/%d/%Y') BETWEEN STR_TO_DATE('" + start_dateKey + "','%m/%d/%Y') AND STR_TO_DATE('" + end_dateKey + "','%m/%d/%Y') "; System.out.println(getServices); conn.rs2 = conn.st2.executeQuery(getServices); while (conn.rs2.next()) { cm = rsp = tb = stis = partner = children = status = ""; System.out.println("client id : " + client_id); cds = 0; cm = conn.rs2.getString("contraceptive_method"); rsp = conn.rs2.getString("rsp"); tb = conn.rs2.getString("screened_tb"); stis = conn.rs2.getString("screened_stis"); partner = conn.rs2.getString("tested_partner"); children = conn.rs2.getString("tested_children"); status = conn.rs2.getString("disclosed_status"); cds = conn.rs2.getInt("cds_given"); if (cm.equals("YES")) { cm1++; } if (rsp.equals("YES")) { rsp1++; } if (tb.equals("YES")) { tb1++; } if (stis.equals("YES")) { stis1++; } if (partner.equals("YES")) { partner1++; } if (children.equals("YES")) { children1++; } if (status.equals("YES")) { status1++; } if (cds > 0) { cds1++; } } // ADD TO THE RESPECTIVE SERVICES PROVIDED======================================== if (cm1 > 0) { cm2++; } if (rsp1 > 0) { rsp2++; } if (tb1 > 0) { tb2++; } if (stis1 > 0) { stis2++; } if (partner1 > 0) { partner2++; } if (children1 > 0) { children2++; } if (status1 > 0) { status2++; } if (cds1 > 0) { cds2++; } } } System.out.println("out==" + pos + "-----------" + cm2 + "-" + rsp2 + "-" + tb2 + "-" + stis2 + "-" + partner2 + "-" + children2 + "-" + status2 + "-" + cds2); if (cm2 > 0 || rsp2 > 0 || tb2 > 0 || stis2 > 0 || partner2 > 0 || children2 > 0 || status2 > 0 || cds2 > 0) { HSSFRow rw5 = shet1.createRow(pos); rw5.setHeightInPoints(45); rw5.setRowStyle(style2); // rw4.createCell(1).setCellValue("Number"); HSSFCell cell1x, cell2x, cell3x, cell4x, cell5x, cell6x, cell7x, cell8x, cell9x, cell10x; cell1x = rw5.createCell(0); cell2x = rw5.createCell(1); cell3x = rw5.createCell(2); cell4x = rw5.createCell(3); cell5x = rw5.createCell(4); cell6x = rw5.createCell(5); cell7x = rw5.createCell(6); cell8x = rw5.createCell(7); cell9x = rw5.createCell(8); cell10x = rw5.createCell(9); cell1x.setCellValue(county_name); cell2x.setCellValue(partner_name); cell3x.setCellValue(cm2); cell4x.setCellValue(rsp2); cell5x.setCellValue(cds2); cell6x.setCellValue(tb2); cell7x.setCellValue(stis2); cell8x.setCellValue(partner2); cell9x.setCellValue(children2); cell10x.setCellValue(status2); cell1x.setCellStyle(stborder); cell2x.setCellStyle(stborder); cell3x.setCellStyle(stborder); cell4x.setCellStyle(stborder); cell5x.setCellStyle(stborder); cell6x.setCellStyle(stborder); cell7x.setCellStyle(stborder); cell8x.setCellStyle(stborder); cell9x.setCellStyle(stborder); cell10x.setCellStyle(stborder); pos++; } } // end of partner } if (conn.rs != null) { conn.rs.close(); } if (conn.st != null) { conn.st.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.st3 != null) { conn.st3.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst != null) { conn.pst.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.pst1 != null) { conn.pst1.close(); } if (conn.conn != null) { conn.conn.close(); } // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=PWP_SERVICES_PROVIDED.xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.staticReportExcel731.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { dbConn conn = new dbConn(); session = request.getSession();//from w w w. j a va2 s. com //-------------------------------------------------------------------------------- //-------------------------------------------------------------------------------- //added later to accomodate the years String subpartnerid = "SubPartnerID"; String subpartnera = "subpartnera"; int monthint = 0; int yearint = 0; reportType = request.getParameter("reportType"); year = Integer.parseInt(request.getParameter("year")); reportDuration = request.getParameter("reportDuration"); yearint = year; // reportType="2"; // year=2015; // reportDuration="3"; prevYear = year - 1; maxYearMonth = 0; // GET REPORT DURATION============================================ startPMTCT = startART = startPEP = startHTC = noPMTCT = noART = noPEP = noHTC = 0; if (reportDuration.equals("1")) { //_________________________________annualy_____________________________________ //solve subpartner table and facil_id first if (yearint <= 2014) { subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else if (yearint > 2015) { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } else if (yearint == 2015) { //this should be skipped since it picks both facil tables. //It has been disabled at the interface position subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } duration = " moh731.yearmonth BETWEEN " + prevYear + "10 AND " + year + "09"; } else if (reportDuration.equals("2")) { //_________________________________SemiAnnualy_________________________________ //oct-mar // if(quarter.equals("1")||quarter.equals("2")){ // if(yearint<=2014){ // subpartnerid="SP_ID"; // subpartnera="subpartnera2014"; // } // else if(yearint>2015) { // subpartnerid="SubPartnerID"; // subpartnera="subpartnera"; // } // else if(yearint==2015){ // //for oct-mar, use old database list // subpartnerid="SP_ID"; // subpartnera="subpartnera2014"; // // } // // } // else if(quarter.equals("3")||quarter.equals("4")){ // //apr-sep // // //apr-sep // // if(yearint<=2014){ // subpartnerid="SP_ID"; // subpartnera="subpartnera2014"; // } // else if(yearint>2015) { // subpartnerid="SubPartnerID"; // subpartnera="subpartnera"; // } // else if(yearint==2015){ // subpartnerid="SubPartnerID"; // subpartnera="subpartnera"; // // } // // } semi_annual = request.getParameter("semi_annual"); // semi_annual="2"; if (semi_annual.equals("1")) { //oct-mar if (yearint <= 2014) { subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else if (yearint > 2015) { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } else if (yearint == 2015) { //for oct-mar, use old database list subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } duration = " moh731.yearmonth BETWEEN " + prevYear + "10 AND " + year + "03"; } else { //apr-sep if (yearint <= 2014) { subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else if (yearint > 2015) { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } else if (yearint == 2015) { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } duration = " moh731.yearmonth BETWEEN " + year + "04 AND " + year + "09"; } } else if (reportDuration.equals("3")) { //quarterly String startMonth, endMonth; //_________________________________Quarterly__________________________________ quarter = request.getParameter("quarter"); //specify subparter table and facil id first //oct-mar if (quarter.equals("1") || quarter.equals("2")) { if (yearint <= 2014) { subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else if (yearint > 2015) { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } else if (yearint == 2015) { //for oct-mar, use old database list subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } } else if (quarter.equals("3") || quarter.equals("4")) { //apr-sep //apr-sep if (yearint <= 2014) { subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else if (yearint > 2015) { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } else if (yearint == 2015) { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } } // quarter="3"; String getMonths = "SELECT months FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonths); if (conn.rs.next() == true) { String months[] = conn.rs.getString(1).split(","); startMonth = months[0]; endMonth = months[2]; if (quarter.equals("1")) { duration = " moh731.yearmonth BETWEEN " + prevYear + "" + startMonth + " AND " + prevYear + "" + endMonth; } else { duration = " moh731.yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + "" + endMonth; } } } else if (reportDuration.equals("4")) { //_____________________________monthly________________________________ //__________________________monthly reports_________________________ //deal with subpartnertable and facilid first monthint = month; if (yearint == 2015) { if (monthint == 10 || monthint == 11 || monthint == 12 || monthint == 1 || monthint == 2 || monthint == 3) { //here use a different subpartner id subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } } else if (yearint <= 2014) { subpartnerid = "SP_ID"; subpartnera = "subpartnera2014"; } else if (yearint > 2015) { subpartnerid = "SubPartnerID"; subpartnera = "subpartnera"; } //--------------------------------------------------------------------------------------- //--------------------------------------------------------------------------------------- month = Integer.parseInt(request.getParameter("month")); // month=5; if (month >= 10) { duration = " moh731.yearmonth=" + prevYear + "" + month; } else { duration = " moh731.yearmonth=" + year + "0" + month; } } else { duration = ""; } // GET FACILITIES TO OUTPUT................................. mflcode = countyName = districtName = facilityName = ""; if (reportType.equals("1")) { facility = ""; facilityName = "ALL APHIA PLUS SUPPORTED HEALTH FACILITIES"; districtName = "ALL"; countyName = "ALL COUNTIES"; mflcode = "NONE"; } else { facilityId = request.getParameter("facility"); String spid = ""; // facilityId="403"; facility = "moh731.SubPartnerID='" + facilityId + "' &&"; String getName = "SELECT subpartnera.SubPartnerNom,district.DistrictNom,county.County,subpartnera.CentreSanteId , SP_ID FROM subpartnera " + "JOIN district ON subpartnera.DistrictID=district.DistrictID JOIN county ON " + "district.CountyID=county.CountyID WHERE subpartnera.SubPartnerID='" + facilityId + "'"; conn.rs = conn.st.executeQuery(getName); if (conn.rs.next() == true) { facilityName = conn.rs.getString(1); districtName = conn.rs.getString(2); countyName = conn.rs.getString(3); mflcode = conn.rs.getString(4); spid = conn.rs.getString(5); } if (subpartnerid.equalsIgnoreCase("SP_ID")) { facility = "moh731.SubPartnerID='" + spid + "' &&"; } } header += "</table>"; String getMaxYearMonth = "SELECT MAX(yearmonth) FROM moh731 WHERE " + facility + " " + duration; conn.rs = conn.st.executeQuery(getMaxYearMonth); if (conn.rs.next() == true) { maxYearMonth = conn.rs.getInt(1); } System.out.println("max year month : " + maxYearMonth); // ^^^^^^^^^^^^^CREATE STATIC AND WRITE STATIC DATA TO THE EXCELL^^^^^^^^^^^^ HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet4 = wb.createSheet("HTC"); HSSFSheet shet1 = wb.createSheet("PMTCT"); HSSFSheet shet2 = wb.createSheet("Care and Treatment"); HSSFSheet shet3 = wb.createSheet("PEP"); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Cambria"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_LEFT); stborder.setWrapText(true); HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); HSSFCellStyle styleHeader = wb.createCellStyle(); styleHeader.setFillForegroundColor(HSSFColor.LIME.index); styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont fontHeader = wb.createFont(); fontHeader.setColor(HSSFColor.DARK_BLUE.index); styleHeader.setFont(fontHeader); styleHeader.setWrapText(true); for (int i = 0; i <= 1; i++) { shet1.setColumnWidth(i, 14000); } for (int i = 0; i <= 1; i++) { shet2.setColumnWidth(i, 14000); } for (int i = 0; i <= 1; i++) { shet3.setColumnWidth(i, 14000); } for (int i = 0; i <= 1; i++) { shet4.setColumnWidth(i, 14000); } shet1.setColumnWidth(2, 4000); shet2.setColumnWidth(2, 4000); shet3.setColumnWidth(2, 4000); shet4.setColumnWidth(2, 4000); String headers = "COUNTY,SUB COUNTY,FACILITY NAME,MFL CODE"; String arrayHeader[] = headers.split(","); int headerno = 0; int valueNo = 0; int arrayCounter = 0; // XSSFRow rw0S1=shet1.createRow(0); HSSFRow rw1S1 = shet1.createRow(0); // XSSFRow rw0S2=shet2.createRow(0); HSSFRow rw1S2 = shet2.createRow(0); // XSSFRow rw0S3=shet3.createRow(0); HSSFRow rw1S3 = shet3.createRow(0); HSSFRow rw1S4 = shet4.createRow(0); String getMonth = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonth); if (conn.rs.next() == true) { monthName = conn.rs.getString(1); } counterPMTCT = counterART = counterPEP = 3; // START OUTPUTTING THE RESULTS================================================= System.out.println("facility : " + facility + " duration : " + duration); prevSection = currentSection = ""; secCounter = 0; String checker = "SELECT " + "SUM(HV0101),SUM(HV0102),SUM(HV0103),SUM(HV0105),SUM(HV0106),SUM(HV0107),SUM(HV0108),SUM(HV0109),SUM(HV0110),SUM(HV0111),SUM(HV0112),SUM(HV0113),SUM(HV0114)," + "SUM(HV0115),SUM(HV0116)," + "SUM(HV0201),SUM(HV0202),SUM(HV0203),SUM(HV0204),SUM(HV0205),SUM(HV0206),SUM(HV0207),SUM(HV0208),SUM(HV0209),SUM(HV0210),SUM(HV0211),SUM(HV0212),SUM(HV0213)," + "SUM(HV0214),SUM(HV0215),SUM(HV0216),SUM(HV0217),SUM(HV0218),SUM(HV0219),SUM(HV0220),SUM(HV0221),SUM(HV0224),SUM(HV0225),SUM(HV0226),SUM(HV0227),SUM(HV0228),SUM(HV0229)," + "SUM(HV0230),SUM(HV0231),SUM(HV0232),SUM(HV0233),SUM(HV0234),SUM(HV0235),SUM(HV0236),SUM(HV0237),SUM(HV0238),SUM(HV0239),SUM(HV0240),SUM(HV0241),SUM(HV0242)," + "SUM(HV0243),SUM(HV0244)," + "SUM(HV0301),SUM(HV0302),SUM(HV0303),SUM(HV0304),SUM(HV0305),SUM(HV0306),SUM(HV0307),SUM(HV0308),SUM(HV0309),SUM(HV0310),SUM(HV0311),SUM(HV0312),SUM(HV0313),SUM(HV0314)," + "SUM(HV0315),SUM(HV0316),SUM(HV0317),SUM(HV0318),SUM(HV0319),SUM(HV0320),SUM(HV0321),SUM(HV0322),SUM(HV0323),SUM(HV0324),SUM(HV0325),SUM(HV0326),SUM(HV0327),SUM(HV0328)," + "SUM(HV0329),SUM(HV0330),SUM(HV0331),SUM(HV0332),SUM(HV0333),SUM(HV0334),SUM(HV0335),SUM(HV0336),SUM(HV0337),SUM(HV0338),SUM(HV0339),SUM(HV0340),SUM(HV0341)," + "SUM(HV0342),SUM(HV0343),SUM(HV0344),SUM(HV0345),SUM(HV0346),SUM(HV0347),SUM(HV0348),SUM(HV0349),SUM(HV0350),SUM(HV0351),SUM(HV0352),SUM(HV0353)," + "SUM(HV0354),SUM(HV0355),SUM(HV0904),SUM(HV0905),SUM(HV0370),SUM(HV0371),SUM(HV0372),SUM(HV0373)," + "SUM(HV0401),SUM(HV0402),SUM(HV0403),SUM(HV0406),SUM(HV0407),SUM(HV0408),SUM(HV0409),SUM(HV0410),SUM(HV0411),SUM(HV0412),SUM(HV0413),SUM(HV0414),SUM(HV0415)," + "SUM(HV0501),SUM(HV0502),SUM(HV0503),SUM(HV0504),SUM(HV0505),SUM(HV0506),SUM(HV0507),SUM(HV0508),SUM(HV0509),SUM(HV0510),SUM(HV0511),SUM(HV0512),SUM(HV0513),SUM(HV0514)," + "SUM(HV0101),SUM(HV0102),SUM(HV0103),SUM(HV0105),SUM(HV0106),SUM(HV0107),SUM(HV0108),SUM(HV0109),SUM(HV0110),SUM(HV0111),SUM(HV0112),SUM(HV0113),SUM(HV0114),SUM(HV0115),SUM(HV0116)," + //added later "SUM(HV0601),SUM(HV0602),SUM(HV0605)," + subpartnera + ".PMTCT, " + subpartnera + ".ART," + subpartnera + ".PEP, " + subpartnera + ".HTC " + "FROM moh731 JOIN " + subpartnera + " ON moh731.SubPartnerID=" + subpartnera + "." + subpartnerid + " WHERE " + facility + " " + duration; System.out.println("@@@ " + checker); conn.rs = conn.st.executeQuery(checker); if (conn.rs.next() == true) { isPMTCT = conn.rs.getInt(subpartnera + ".PMTCT"); isART = conn.rs.getInt(subpartnera + ".ART"); isPEP = conn.rs.getInt(subpartnera + ".PEP"); isHTC = conn.rs.getInt(subpartnera + ".HTC"); String headerValues = countyName + "," + districtName + "," + facilityName + "," + mflcode; String arrayValues[] = headerValues.split(","); String headerValue = ""; pos = 0; // CREATE HEADERS for (String headername : arrayHeader) { headerValue = arrayValues[arrayCounter]; HSSFRow rw1S10 = shet1.createRow(pos); HSSFRow rw1S20 = shet2.createRow(pos); HSSFRow rw1S30 = shet3.createRow(pos); HSSFRow rw1S40 = shet4.createRow(pos); HSSFCell S1cell = rw1S10.createCell(0); S1cell.setCellValue(headername); S1cell.setCellStyle(stylex); HSSFCell S1cellX = rw1S10.createCell(1); S1cellX.setCellValue(headerValue); S1cellX.setCellStyle(stylex); S1cellX = rw1S10.createCell(2); S1cellX.setCellValue(""); S1cellX.setCellStyle(stylex); S1cellX = rw1S10.createCell(3); S1cellX.setCellValue(""); S1cellX.setCellStyle(stylex); HSSFCell S2cell = rw1S20.createCell(0); S2cell.setCellValue(headername); S2cell.setCellStyle(stylex); HSSFCell S2cellX = rw1S20.createCell(1); S2cellX.setCellValue(headerValue); S2cellX.setCellStyle(stylex); S2cellX = rw1S20.createCell(2); S2cellX.setCellValue(""); S2cellX.setCellStyle(stylex); S2cellX = rw1S20.createCell(3); S2cellX.setCellValue(""); S2cellX.setCellStyle(stylex); HSSFCell S3cell = rw1S30.createCell(0); S3cell.setCellValue(headername); S3cell.setCellStyle(stylex); HSSFCell S3cellX = rw1S30.createCell(1); S3cellX.setCellValue(headerValue); S3cellX.setCellStyle(stylex); S3cellX = rw1S30.createCell(2); S3cellX.setCellValue(""); S3cellX.setCellStyle(stylex); S3cellX = rw1S30.createCell(3); S3cellX.setCellValue(""); S3cellX.setCellStyle(stylex); //_______________________________ HSSFCell S4cell = rw1S40.createCell(0); S4cell.setCellValue(headername); S4cell.setCellStyle(stylex); HSSFCell S4cellX = rw1S40.createCell(1); S4cellX.setCellValue(headerValue); S4cellX.setCellStyle(stylex); S4cellX = rw1S40.createCell(2); S4cellX.setCellValue(""); S4cellX.setCellStyle(stylex); S4cellX = rw1S40.createCell(3); S4cellX.setCellValue(""); S4cellX.setCellStyle(stylex); arrayCounter++; pos++; } // pos+=1; // OUTPUT ELEMENT HEADING HSSFRow rw1S10 = shet1.createRow(pos); HSSFRow rw1S20 = shet2.createRow(pos); HSSFRow rw1S30 = shet3.createRow(pos); HSSFRow rw1S40 = shet4.createRow(pos); rw1S10.setHeightInPoints(25); rw1S20.setHeightInPoints(25); rw1S30.setHeightInPoints(25); rw1S40.setHeightInPoints(25); HSSFCell S1cell = rw1S10.createCell(0); S1cell.setCellValue("SUB SECTION"); S1cell.setCellStyle(styleHeader); HSSFCell S1cellX = rw1S10.createCell(1); S1cellX.setCellValue("ELEMENT TITLE"); S1cellX.setCellStyle(styleHeader); HSSFCell S1cellX2 = rw1S10.createCell(2); S1cellX2.setCellValue("LABEL"); S1cellX2.setCellStyle(styleHeader); S1cellX2 = rw1S10.createCell(3); S1cellX2.setCellValue("VALUE"); S1cellX2.setCellStyle(styleHeader); HSSFCell S2cell = rw1S20.createCell(0); S2cell.setCellValue("SUB SECTION"); S2cell.setCellStyle(styleHeader); HSSFCell S2cellX = rw1S20.createCell(1); S2cellX.setCellValue("ELEMENT TITLE"); S2cellX.setCellStyle(styleHeader); HSSFCell S2cellX2 = rw1S20.createCell(2); S2cellX2.setCellValue("LABEL"); S2cellX2.setCellStyle(styleHeader); S2cellX2 = rw1S20.createCell(3); S2cellX2.setCellValue("VALUE"); S2cellX2.setCellStyle(styleHeader); HSSFCell S3cell = rw1S30.createCell(0); S3cell.setCellValue("SUB SECTION"); S3cell.setCellStyle(styleHeader); HSSFCell S3cellX = rw1S30.createCell(1); S3cellX.setCellValue("ELEMENT TITLE"); S3cellX.setCellStyle(styleHeader); HSSFCell S3cellX2 = rw1S30.createCell(2); S3cellX2.setCellValue("LABEL"); S3cellX2.setCellStyle(styleHeader); S3cellX2 = rw1S30.createCell(3); S3cellX2.setCellValue("VALUE"); S3cellX2.setCellStyle(styleHeader); //--------------------htc HSSFCell S4cell = rw1S40.createCell(0); S4cell.setCellValue("SUB SECTION"); S4cell.setCellStyle(styleHeader); HSSFCell S4cellX = rw1S40.createCell(1); S4cellX.setCellValue("ELEMENT TITLE"); S4cellX.setCellStyle(styleHeader); HSSFCell S4cellX2 = rw1S40.createCell(2); S4cellX2.setCellValue("LABEL"); S4cellX2.setCellStyle(styleHeader); S4cellX2 = rw1S40.createCell(3); S4cellX2.setCellValue("VALUE"); S4cellX2.setCellStyle(styleHeader); elementCounter = 1; valueCounter = 1; specialElement = 0; String getCummulatives = "SELECT " // + "SUM(HV0301),SUM(HV0302)," + "SUM(HV0303),SUM(HV0304),SUM(HV0305),SUM(HV0306),SUM(HV0307)," + "SUM(HV0314),SUM(HV0315),SUM(HV0316),SUM(HV0317),SUM(HV0318),SUM(HV0319)," + "SUM(HV0328),SUM(HV0329),SUM(HV0330),SUM(HV0331),SUM(HV0332),SUM(HV0333),SUM(HV0334),SUM(HV0335)," + "SUM(HV0336),SUM(HV0337),SUM(HV0338),SUM(HV0339),SUM(HV0340),SUM(HV0341),SUM(HV0342),SUM(HV0343),SUM(HV0344), " + "SUM(HV0350),SUM(HV0351),SUM(HV0352),SUM(HV0353),SUM(HV0354),SUM(HV0355) " + "FROM moh731 join subpartnera on moh731.subpartnerid=subpartnera.subpartnerid WHERE " + facility + " art=1 && yearmonth=" + maxYearMonth; conn.rs2 = conn.st2.executeQuery(getCummulatives); if (conn.rs2.next() == true) { System.out.println("entered to get cumulatives : " + maxYearMonth); int j = 5; int i = 5; int k = 5; int l = 5; String getElements = "SELECT subsection,shortlabel,label FROM pivottable WHERE form='moh731' ORDER BY tableid"; conn.rs1 = conn.st1.executeQuery(getElements); while (conn.rs1.next()) { // subsection,shortlabel,label; subsection = conn.rs1.getString(1); shortlabel = conn.rs1.getString(2); label = conn.rs1.getString(3); elementCounter++; valueCounter = elementCounter + 4; //pmtct if (elementCounter >= 17 && elementCounter <= 58) { if (isPMTCT == 1 && j <= 47) { int valuePos = j + 15 - 4; HSSFRow rw1S11 = shet1.createRow(j); HSSFCell S1cell1 = rw1S11.createCell(0); S1cell1.setCellValue(subsection); S1cell1.setCellStyle(stborder); HSSFCell S1cellX1 = rw1S11.createCell(1); S1cellX1.setCellValue(shortlabel); S1cellX1.setCellStyle(stborder); HSSFCell S1cellX21 = rw1S11.createCell(2); S1cellX21.setCellValue(label); S1cellX21.setCellStyle(stborder); S1cellX21 = rw1S11.createCell(3); S1cellX21.setCellValue(conn.rs.getInt(valuePos)); S1cellX21.setCellStyle(stborder); currentSection = subsection; if (prevSection.equals(currentSection) && !prevSection.equals("")) { secCounter++; // System.out.println("THey are equal :"+prevSection+" current sec: "+currentSection); } else if (j == 5) { prevSection = currentSection = subsection; //secCounter++; // System.out.println("entered j=5 :"+j+" :"+prevSection+" current sec: "+currentSection); } else if (!prevSection.equals(currentSection)) { int startMerger = j - secCounter - 1; int endMerger = j - 1; shet1.addMergedRegion(new CellRangeAddress(startMerger, endMerger, 0, 0)); secCounter = 0; // System.out.println("merged cells from :"+startMerger+" to :"+endMerger); } else { System.out.println("cant think anymore"); } prevSection = currentSection; j++; // System.out.println("j values : "+j); if (j == 47) { System.out.println("entered end here j " + j); int startMerger = j - secCounter - 1; int endMerger = j - 1; shet1.addMergedRegion(new CellRangeAddress(startMerger, endMerger, 0, 0)); secCounter = 0; prevSection = currentSection = ""; } } } //art if (elementCounter >= 59 && elementCounter <= 119) { if (isART == 1 && i <= 66) { int valuePos = i + 57 - 4; HSSFRow rw1S11 = shet2.createRow(i); HSSFCell S1cell1 = rw1S11.createCell(0); S1cell1.setCellValue(subsection); S1cell1.setCellStyle(stborder); HSSFCell S1cellX1 = rw1S11.createCell(1); S1cellX1.setCellValue(shortlabel); S1cellX1.setCellStyle(stborder); HSSFCell S1cellX21 = rw1S11.createCell(2); S1cellX21.setCellValue(label); S1cellX21.setCellStyle(stborder); if (elementCounter >= 61 && elementCounter <= 65) { specialElement++; // System.out.println("entered on cumus for ctx>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"); // System.out.println("Value is >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>"+conn.rs2.getInt(specialElement)); S1cellX21 = rw1S11.createCell(3); S1cellX21.setCellValue(conn.rs2.getInt(specialElement)); // S1cellX21.setCellValue(""); S1cellX21.setCellStyle(stborder); } else if (elementCounter >= 72 && elementCounter <= 77) { specialElement++; S1cellX21 = rw1S11.createCell(3); S1cellX21.setCellValue(conn.rs2.getInt(specialElement)); S1cellX21.setCellStyle(stborder); } else if (elementCounter >= 86 && elementCounter <= 91) { specialElement++; S1cellX21 = rw1S11.createCell(3); S1cellX21.setCellValue(conn.rs2.getInt(specialElement)); S1cellX21.setCellStyle(stborder); } else if (elementCounter >= 92 && elementCounter <= 102) { specialElement++; S1cellX21 = rw1S11.createCell(3); S1cellX21.setCellValue(conn.rs2.getInt(specialElement)); S1cellX21.setCellStyle(stborder); } else if (elementCounter >= 108 && elementCounter <= 113) { specialElement++; S1cellX21 = rw1S11.createCell(3); S1cellX21.setCellValue(conn.rs2.getInt(specialElement)); S1cellX21.setCellStyle(stborder); } else { S1cellX21 = rw1S11.createCell(3); S1cellX21.setCellValue(conn.rs.getInt(valuePos)); S1cellX21.setCellStyle(stborder); } currentSection = subsection; if (prevSection.equals(currentSection) && !prevSection.equals("")) { secCounter++; // System.out.println("THey are equal :"+prevSection+" current sec: "+currentSection); } else if (i == 5) { prevSection = currentSection = subsection; //secCounter++; // System.out.println("entered j=5 :"+i+" :"+prevSection+" current sec: "+currentSection); } else if (!prevSection.equals(currentSection)) { int startMerger = i - secCounter - 1; int endMerger = i - 1; shet2.addMergedRegion(new CellRangeAddress(startMerger, endMerger, 0, 0)); secCounter = 0; // System.out.println("merged cells from :"+startMerger+" to :"+endMerger); } else { // System.out.println("cant think anymore"); } prevSection = currentSection; i++; if (i == 66) { // System.out.println("entered end here i "+i); int startMerger = i - secCounter - 1; int endMerger = i - 1; shet2.addMergedRegion(new CellRangeAddress(startMerger, endMerger, 0, 0)); secCounter = 0; prevSection = currentSection = ""; } } } //PEP if (elementCounter >= 133 && elementCounter <= 146) { if (isPEP == 1 && k <= 18) { int valuePos = k + 131 - 4; System.out.println("k values : " + k); HSSFRow rw1S11 = shet3.createRow(k); HSSFCell S1cell1 = rw1S11.createCell(0); S1cell1.setCellValue(subsection); S1cell1.setCellStyle(stborder); HSSFCell S1cellX1 = rw1S11.createCell(1); S1cellX1.setCellValue(shortlabel); S1cellX1.setCellStyle(stborder); HSSFCell S1cellX21 = rw1S11.createCell(2); S1cellX21.setCellValue(label); S1cellX21.setCellStyle(stborder); S1cellX21 = rw1S11.createCell(3); S1cellX21.setCellValue(conn.rs.getInt(valuePos)); S1cellX21.setCellStyle(stborder); currentSection = subsection; if (prevSection.equals(currentSection) && !prevSection.equals("")) { secCounter++; } else if (k == 5) { prevSection = currentSection = subsection; } else if (!prevSection.equals(currentSection)) { int startMerger = k - secCounter - 1; int endMerger = k - 1; if (startMerger == endMerger) { } else { shet3.addMergedRegion(new CellRangeAddress(startMerger, endMerger, 0, 0)); } secCounter = 0; System.out.println("merged cells from :" + startMerger + " to :" + endMerger); } else { System.out.println("cant think anymore"); } prevSection = currentSection; k++; if (k == 18) { int startMerger = k - secCounter - 1; int endMerger = k; shet3.addMergedRegion(new CellRangeAddress(startMerger, endMerger, 0, 0)); secCounter = 0; prevSection = currentSection = ""; } } } //==========================HTC=============================== if (elementCounter <= 16) { if (isHTC == 1 && l <= 19) { int valuePos = l - 4; System.out.println("l values : " + l); HSSFRow rw1S11 = shet4.createRow(l); HSSFCell S1cell1 = rw1S11.createCell(0); S1cell1.setCellValue(subsection); S1cell1.setCellStyle(stborder); HSSFCell S1cellX1 = rw1S11.createCell(1); S1cellX1.setCellValue(shortlabel); S1cellX1.setCellStyle(stborder); HSSFCell S1cellX21 = rw1S11.createCell(2); S1cellX21.setCellValue(label); S1cellX21.setCellStyle(stborder); S1cellX21 = rw1S11.createCell(3); S1cellX21.setCellValue(conn.rs.getInt(valuePos)); S1cellX21.setCellStyle(stborder); currentSection = subsection; if (prevSection.equals(currentSection) && !prevSection.equals("")) { secCounter++; } else if (l == 5) { prevSection = currentSection = subsection; } else if (!prevSection.equals(currentSection)) { int startMerger = l - secCounter - 1; int endMerger = l - 1; if (startMerger == endMerger) { } else { shet4.addMergedRegion(new CellRangeAddress(startMerger, endMerger, 0, 0)); } secCounter = 0; System.out.println("merged cells from :" + startMerger + " to :" + endMerger); } else { System.out.println("cant think anymore"); } prevSection = currentSection; l++; if (l == 19) { int startMerger = l - secCounter - 1; int endMerger = l; shet4.addMergedRegion(new CellRangeAddress(startMerger, endMerger, 0, 0)); secCounter = 0; prevSection = currentSection = ""; } } } } } System.out.println("Data already exist loading............................"); counter++; } System.out.println("Validity checker : " + isValidated); if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.conn != null) { conn.conn.close(); } IdGenerator IG = new IdGenerator(); createdOn = IG.CreatedOn(); // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=MOH731_STATIC_REPORT_CREATED_" + createdOn.trim() + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:reports.verificationreports.java
protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, SQLException { session = request.getSession();/*from w w w . ja va 2s . com*/ dbConn conn = new dbConn(); allFacilities.clear(); year = Integer.parseInt(request.getParameter("year")); reportDuration = request.getParameter("reportDuration"); String headerART[] = "County,Sub County,Health Facility,MFL Code,Type of support,Numerator,<1,1-4Y,5-14Y,15-19Y,20+Y,<1,1-4Y,5-14Y,15-19Y,20+Y,Numerator,<1,1-4Y,5-9Y,10-14Y,15-19Y,20-24Y,25-49Y,50+Y,<1,1-4Y,5-9Y,10-14Y,15-19Y,20-24Y,25-49Y,50+Y" .split(","); String headerCARE[] = "County,Sub County,Health Facility,MFL Code,Type of support,Numerator,<1,1-4Y,5-9Y,10-14Y,15-19Y,20-24Y,25-49Y,50+Y,<1,1-4Y,5-9Y,10-14Y,15-19Y,20-24Y,25-49Y,50+Y,NUMERATOR,<1,1-4Y,5-9Y,10-14Y,15-19Y,20-24Y,25-49Y,50+Y,<1,1-4Y,5-9Y,10-14Y,15-19Y,20-24Y,25-49Y,50+Y" .split(","); String headerPMTCT[] = "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,Numerator,Denominator,Life-long ART:New,Life-long ART: Already on treatment at the beginning of the current pregnancy,Maternal Triple-Drug ARV,Maternal AZT,Single-dose nevirapine(with or without tail),Numerator,Infants who received a virologic test within 2 months of birth, Infants who received their first virologic HIV test between 2 and 12 months of age,Infants with a postive virologic test results within 2 months of birth, Infants with a postive virologic test resultsbetween 2 and 12 months of age,Numerator,Known postive at entry,New postives,Denominator,Numerator " .split(","); // year=2015; // reportDuration="4"; String facilityIds1 = ""; period = ""; prevYear = year - 1; maxYearMonth = 0; facilityIds = "("; facilityIds1 = "("; artpos = carepos = pmtctpos = 0; // GET REPORT DURATION============================================ if (reportDuration.equals("1")) { duration = " moh731.yearmonth BETWEEN " + prevYear + "10 AND " + year + "09"; period = "DATIM ANNUAL DATA REPORT FOR PEPFAR YEAR : " + year; } else if (reportDuration.equals("2")) { semi_annual = request.getParameter("semi_annual"); // semi_annual="2"; if (semi_annual.equals("1")) { duration = " moh731.yearmonth BETWEEN " + prevYear + "10 AND " + year + "03"; period = "DATIM SEMI - ANNUAL DATA REPORT FOR : OCT " + prevYear + " to MARCH " + year; } else { duration = " moh731.yearmonth BETWEEN " + year + "04 AND " + year + "09"; period = "DATIM SEMI - ANNUAL DATA REPORT FOR : APRIL " + year + " to SEPT " + year; } } else if (reportDuration.equals("3")) { String startMonth, endMonth; quarter = request.getParameter("quarter"); // quarter="3"; String getMonths = "SELECT months,name FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonths); if (conn.rs.next() == true) { String months[] = conn.rs.getString(1).split(","); startMonth = months[0]; endMonth = months[2]; if (quarter.equals("1")) { duration = " moh731.yearmonth BETWEEN " + prevYear + "" + startMonth + " AND " + prevYear + "" + endMonth; period = "DATIM QUARTERLY DATA REPORT FOR : " + conn.rs.getString(2).replace("-", " " + prevYear + " TO ") + " " + prevYear + ""; } else { duration = " moh731.yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + "" + endMonth; period = "DATIM QUARTERLY DATA REPORT FOR : " + conn.rs.getString(2).replace("-", " " + year + " TO ") + " " + year + ""; } } } else if (reportDuration.equals("4")) { month = Integer.parseInt(request.getParameter("month")); // month=5; String getMonthName = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonthName); if (conn.rs.next() == true) { if (month >= 10) { duration = " moh731.yearmonth=" + prevYear + "" + month; period = "DATIM MONTHLY DATA REPORT FOR : " + conn.rs.getString(1) + "(" + prevYear + ")"; } else { duration = " moh731.yearmonth=" + year + "0" + month; period = "DATIM MONTHLY DATA REPORT FOR : " + conn.rs.getString(1) + "(" + year + ")"; } } } else { duration = ""; } if (request.getParameter("subcounty") != null && !request.getParameter("subcounty").equals("")) { String subcounty = request.getParameter("subcounty"); String getDist = "SELECT subpartnera.SubPartnerID FROM subpartnera " + "JOIN district ON subpartnera.DistrictID=district.DistrictID " + "WHERE district.DistrictID='" + subcounty + "'"; conn.rs = conn.st.executeQuery(getDist); while (conn.rs.next()) { allFacilities.add(conn.rs.getString(1)); facilityIds += " moh731.SubPartnerID='" + conn.rs.getString(1) + "' || "; facilityIds1 += " moh711.SubPartnerID='" + conn.rs.getString(1) + "' || "; } facilityIds = facilityIds.substring(0, facilityIds.length() - 3); facilityIds += ") && "; facilityIds1 = facilityIds1.substring(0, facilityIds1.length() - 3); facilityIds1 += ") && "; } else { if (request.getParameter("county") != null && !request.getParameter("county").equals("")) { String county = request.getParameter("county"); String getCounty = "SELECT subpartnera.SubPartnerID FROM subpartnera " + "JOIN district ON subpartnera.DistrictID=district.DistrictID " + "JOIN county ON district.CountyID=county.CountyID WHERE county.CountyID='" + county + "'"; conn.rs = conn.st.executeQuery(getCounty); while (conn.rs.next()) { allFacilities.add(conn.rs.getString(1)); facilityIds += " moh731.SubPartnerID='" + conn.rs.getString(1) + "' || "; facilityIds1 += " moh711.SubPartnerID='" + conn.rs.getString(1) + "' || "; } facilityIds = facilityIds.substring(0, facilityIds.length() - 3); facilityIds += ") && "; facilityIds1 = facilityIds1.substring(0, facilityIds1.length() - 3); facilityIds1 += ") && "; } else { facilityIds = ""; facilityIds1 = ""; } } System.out.println("period is : " + period); // GET FACILITIES TO OUTPUT................................. mflcode = countyName = districtName = facilityName = ""; // facilityId=request.getParameter("facility"); // facilityIds="403"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet shet1 = wb.createSheet("ART"); HSSFSheet shet2 = wb.createSheet("CARE"); HSSFSheet shet3 = wb.createSheet("HTC "); HSSFSheet shetPMTCT = wb.createSheet("PMTCT"); HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 18); font.setFontName("Arial Black"); font.setColor((short) 0000); CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setFontName("Arial Black"); font2.setColor((short) 0000); CellStyle style2 = wb.createCellStyle(); style2.setFont(font2); HSSFCellStyle stborder = wb.createCellStyle(); stborder.setBorderTop(HSSFCellStyle.BORDER_THIN); stborder.setBorderBottom(HSSFCellStyle.BORDER_THIN); stborder.setBorderLeft(HSSFCellStyle.BORDER_THIN); stborder.setBorderRight(HSSFCellStyle.BORDER_THIN); stborder.setAlignment(HSSFCellStyle.ALIGN_CENTER); stborder.setWrapText(true); // for the red color HSSFCellStyle redstyle = wb.createCellStyle(); redstyle.setFillForegroundColor(HSSFColor.RED.index); redstyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); redstyle.setBorderTop(HSSFCellStyle.BORDER_THIN); redstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); redstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); redstyle.setBorderRight(HSSFCellStyle.BORDER_THIN); redstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); redstyle.setWrapText(true); // HSSFCellStyle stylex = wb.createCellStyle(); //stylex.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); //stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); // stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); // stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); // stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); // stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); // //HSSFFont fontx = wb.createFont(); //fontx.setColor(HSSFColor.DARK_BLUE.index); //stylex.setFont(fontx); //stylex.setWrapText(true); // HSSFCellStyle stylemainHeader = wb.createCellStyle(); //stylemainHeader.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); //stylemainHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // stylemainHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); // stylemainHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); // stylemainHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); // stylemainHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); // stylemainHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); // stylemainHeader.setWrapText(true); HSSFCellStyle styleHeader = wb.createCellStyle(); styleHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); styleHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleHeader.setWrapText(true); HSSFCellStyle styleminiHeader = wb.createCellStyle(); styleminiHeader.setFillForegroundColor(HSSFColor.ORCHID.index); styleminiHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); styleminiHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); styleminiHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleminiHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleminiHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); styleminiHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleminiHeader.setWrapText(true); HSSFFont fontHeader = wb.createFont(); fontHeader.setColor(HSSFColor.DARK_BLUE.index); styleHeader.setFont(fontHeader); styleHeader.setWrapText(true); for (int i = 3; i <= headerPMTCT.length; i++) { shetPMTCT.setColumnWidth(i, 4000); } for (int i = 0; i <= 1; i++) { shetPMTCT.setColumnWidth(i, 5000); } shetPMTCT.setColumnWidth(2, 8000); for (int i = 5; i <= 16; i++) { shetPMTCT.setColumnWidth(i, 4000); } for (int i = 3; i <= 33; i++) { shet1.setColumnWidth(i, 2000); } for (int i = 0; i <= 1; i++) { shet1.setColumnWidth(i, 5000); } shet1.setColumnWidth(2, 8000); for (int i = 3; i <= 33; i++) { shet2.setColumnWidth(i, 2000); } // for sheet3 for (int i = 3; i <= 42; i++) { shet3.setColumnWidth(i, 2000); } for (int i = 0; i <= 1; i++) { shet2.setColumnWidth(i, 5000); } for (int i = 0; i <= 1; i++) { shet3.setColumnWidth(i, 5000); } shet2.setColumnWidth(2, 8000); shet1.setColumnWidth(5, 3500); shet1.setColumnWidth(16, 3500); shet2.setColumnWidth(5, 3500); shet2.setColumnWidth(22, 3500); shet3.setColumnWidth(7, 3500); shet3.setColumnWidth(25, 3500); // ART AND CARE HEADERS============================================================================================ HSSFRow rw00shet1 = shet1.createRow(1); rw00shet1.setHeightInPoints(30); HSSFRow rw00shet2 = shet2.createRow(1); rw00shet2.setHeightInPoints(30); HSSFCell c011; for (int j = 0; j < headerART.length; j++) { c011 = rw00shet1.createCell(j); c011.setCellStyle(styleHeader); } for (int j = 0; j < headerCARE.length; j++) { c011 = rw00shet2.createCell(j); c011.setCellStyle(styleHeader); } c011 = rw00shet1.getCell(0); c011.setCellValue(period); c011 = rw00shet1.getCell(5); c011.setCellValue("CURRENT ON ART"); c011 = rw00shet1.getCell(16); c011.setCellValue("NEW ON ART"); shet1.addMergedRegion(new CellRangeAddress(1, 1, 5, 15)); shet1.addMergedRegion(new CellRangeAddress(1, 1, 16, 32)); c011 = rw00shet2.getCell(0); c011.setCellValue(period); c011 = rw00shet2.getCell(5); c011.setCellValue("CURRENTLY ON CARE"); c011 = rw00shet2.getCell(22); c011.setCellValue("NEW ON CARE"); shet2.addMergedRegion(new CellRangeAddress(1, 1, 5, 21)); shet2.addMergedRegion(new CellRangeAddress(1, 1, 22, 38)); HSSFRow rw0shet1 = shet1.createRow(2); rw0shet1.setHeightInPoints(30); HSSFRow rw0shet2 = shet2.createRow(2); rw0shet2.setHeightInPoints(30); HSSFCell c001; for (int j = 0; j < headerART.length; j++) { c001 = rw0shet1.createCell(j); c001.setCellStyle(styleHeader); } for (int j = 0; j < headerCARE.length; j++) { c001 = rw0shet2.createCell(j); c001.setCellStyle(styleHeader); } c001 = rw0shet1.getCell(5); c001.setCellValue("NUMERATOR"); c001 = rw0shet1.getCell(16); c001.setCellValue("NUMERATOR"); c001 = rw0shet1.getCell(6); c001.setCellValue("FEMALE"); c001 = rw0shet1.getCell(11); c001.setCellValue("MALE"); c001 = rw0shet1.getCell(17); c001.setCellValue("FEMALE"); c001 = rw0shet1.getCell(25); c001.setCellValue("MALE"); shet1.addMergedRegion(new CellRangeAddress(2, 2, 6, 10)); shet1.addMergedRegion(new CellRangeAddress(2, 2, 11, 15)); shet1.addMergedRegion(new CellRangeAddress(2, 2, 17, 24)); shet1.addMergedRegion(new CellRangeAddress(2, 2, 25, 32)); c001 = rw0shet2.getCell(5); c001.setCellValue("NUMERATOR"); c001 = rw0shet2.getCell(22); c001.setCellValue("NUMERATOR"); c001 = rw0shet2.getCell(6); c001.setCellValue("FEMALE"); c001 = rw0shet2.getCell(14); c001.setCellValue("MALE"); c001 = rw0shet2.getCell(23); c001.setCellValue("FEMALE"); c001 = rw0shet2.getCell(31); c001.setCellValue("MALE"); shet2.addMergedRegion(new CellRangeAddress(2, 2, 6, 13)); shet2.addMergedRegion(new CellRangeAddress(2, 2, 14, 21)); shet2.addMergedRegion(new CellRangeAddress(2, 2, 23, 30)); shet2.addMergedRegion(new CellRangeAddress(2, 2, 31, 38)); //ROW 3 FOR PAEDS STARTS HERE HSSFRow rw1shet1 = shet1.createRow(3); rw1shet1.setHeightInPoints(30); HSSFRow rw1shet2 = shet2.createRow(3); rw1shet2.setHeightInPoints(30); HSSFCell c01; for (int j = 0; j < headerART.length; j++) { c01 = rw1shet1.createCell(j); c01.setCellStyle(styleHeader); } for (int j = 0; j < headerCARE.length; j++) { c01 = rw1shet2.createCell(j); c01.setCellStyle(styleHeader); } c01 = rw1shet1.getCell(6); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet1.getCell(9); c01.setCellValue("Adults 15+ Yr"); c01 = rw1shet1.getCell(11); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet1.getCell(14); c01.setCellValue("Adults 15+ Yr"); c01 = rw1shet1.getCell(17); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet1.getCell(21); c01.setCellValue("Adults 15+ Yr"); c01 = rw1shet1.getCell(25); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet1.getCell(29); c01.setCellValue("Adults 15+ Yr"); shet1.addMergedRegion(new CellRangeAddress(3, 3, 6, 8)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 9, 10)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 11, 13)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 14, 15)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 17, 20)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 21, 24)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 25, 28)); shet1.addMergedRegion(new CellRangeAddress(3, 3, 29, 32)); c01 = rw1shet2.getCell(6); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet2.getCell(10); c01.setCellValue("Adults 15+ Yr"); c01 = rw1shet2.getCell(14); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet2.getCell(18); c01.setCellValue("Adults 15+ Yr"); c01 = rw1shet2.getCell(23); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet2.getCell(27); c01.setCellValue("Adults 15+ Yr"); c01 = rw1shet2.getCell(31); c01.setCellValue("Paeds <15yrs"); c01 = rw1shet2.getCell(35); c01.setCellValue("Adults 15+ Yr"); shet2.addMergedRegion(new CellRangeAddress(3, 3, 6, 9)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 10, 13)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 14, 17)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 18, 21)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 23, 26)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 27, 30)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 31, 34)); shet2.addMergedRegion(new CellRangeAddress(3, 3, 35, 38)); shet1.addMergedRegion(new CellRangeAddress(1, 3, 0, 4)); shet2.addMergedRegion(new CellRangeAddress(1, 3, 0, 4)); HSSFRow rw2shet1 = shet1.createRow(4); rw2shet1.setHeightInPoints(30); HSSFRow rw2shet2 = shet2.createRow(4); rw2shet2.setHeightInPoints(30); HSSFCell c11; for (int headerpos = 0; headerpos < headerART.length; headerpos++) { String headerInfor = headerART[headerpos]; c11 = rw2shet1.createCell(headerpos); c11.setCellValue(headerInfor); c11.setCellStyle(styleHeader); } System.out.println("art header length : " + headerART.length); for (int headerpos = 0; headerpos < headerCARE.length; headerpos++) { String headerInfor = headerCARE[headerpos]; c11 = rw2shet2.createCell(headerpos); c11.setCellValue(headerInfor); c11.setCellStyle(styleHeader); } shet1.addMergedRegion(new CellRangeAddress(2, 4, 5, 5)); shet2.addMergedRegion(new CellRangeAddress(2, 4, 5, 5)); shet1.addMergedRegion(new CellRangeAddress(2, 4, 16, 16)); shet2.addMergedRegion(new CellRangeAddress(2, 4, 22, 22)); // PMTCT HEADER===================================================================================== HSSFRow rw0shetPMTCT = shetPMTCT.createRow(1); rw0shetPMTCT.setHeightInPoints(30); for (int j = 0; j < headerPMTCT.length; j++) { c001 = rw0shetPMTCT.createCell(j); c001.setCellStyle(styleHeader); } c001 = rw0shetPMTCT.getCell(0); c001.setCellValue(period); c001 = rw0shetPMTCT.getCell(5); c001.setCellValue("PMTCT_FO"); c001 = rw0shetPMTCT.getCell(17); c001.setCellValue("PMTCT_ARV"); c001 = rw0shetPMTCT.getCell(24); c001.setCellValue("PMTCT_EID"); c001 = rw0shetPMTCT.getCell(29); c001.setCellValue("PMTCT_STAT (Numerator)"); c001 = rw0shetPMTCT.getCell(32); c001.setCellValue("PMTCT_STAT (Denominator)"); c001 = rw0shetPMTCT.getCell(33); c001.setCellValue("PMTCT_CTX"); shetPMTCT.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); shetPMTCT.addMergedRegion(new CellRangeAddress(1, 1, 5, 16)); shetPMTCT.addMergedRegion(new CellRangeAddress(1, 1, 17, 23)); shetPMTCT.addMergedRegion(new CellRangeAddress(1, 1, 24, 28)); shetPMTCT.addMergedRegion(new CellRangeAddress(1, 1, 29, 31)); // shetPMTCT.addMergedRegion(new CellRangeAddress(2,2,25,32)); HSSFRow rw2shetPMTCT = shetPMTCT.createRow(2); rw2shetPMTCT.setHeightInPoints(50); for (int headerpos = 0; headerpos < headerPMTCT.length; headerpos++) { String headerInfor = headerPMTCT[headerpos]; c11 = rw2shetPMTCT.createCell(headerpos); c11.setCellValue(headerInfor); c11.setCellStyle(styleHeader); } System.out.println("art header length : " + headerPMTCT.length); artpos = 4; pmtctpos = 3; totalNewART = totalCurrentART = totalNewCARE = totalCurrentCARE = 0; // String getData="SELECT subpartnera.SubPartnerNom,district.DistrictNom,county.County," // + "subpartnera.CentreSanteId,ART_Support,PMTCT_Support," // + "SUM(HV0308),SUM(HV0309),SUM(HV0310),SUM(HV0311),SUM(HV0312)," // + "SUM(HV0320),SUM(HV0321),SUM(HV0322),SUM(HV0323),SUM(HV0324)," // + "subpartnera.SubPartnerID FROM moh731 JOIN subpartnera " // + "ON moh731.SubPartnerID=subpartnera.SubPartnerID " // + "JOIN district ON subpartnera.DistrictID=district.DistrictID JOIN county ON " // + "district.CountyID=county.CountyID" // + " WHERE " // + " "+facilityIds+" "+duration+" && (subpartnera.PMTCT=1 || ART=1) " // + "GROUP BY moh731.SubPartnerID " ; String getData = "SELECT subpartnera.SubPartnerNom,district.DistrictNom,county.County," + "subpartnera.CentreSanteId,ART_Support,PMTCT_Support," + "SUM(HV0308),SUM(HV0309),SUM(HV0310),SUM(HV0311),SUM(HV0312)," + "SUM(HV0320),SUM(HV0321),SUM(HV0322),SUM(HV0323),SUM(HV0324)," + "subpartnera.SubPartnerID," + "SUM(HV0205),SUM(HV0209),SUM(HV0210),SUM(HV0216),SUM(HV0217)," + "SUM(HV0224),SUM(HV0225),SUM(HV0227),SUM(HV0229),SUM(HV0230),SUM(HV0231),SUM(HV0232)," + "SUM(HV0301),SUM(HV0206),SUM(HV0207),SUM(HV0208) " + " FROM moh731 JOIN subpartnera " + "ON moh731.SubPartnerID=subpartnera.SubPartnerID " + "JOIN district ON subpartnera.DistrictID=district.DistrictID JOIN county ON " + "district.CountyID=county.CountyID" + " WHERE " + " " + facilityIds + " " + duration + " && (subpartnera.PMTCT=1 || ART=1) " + "GROUP BY moh731.SubPartnerID "; System.out.println("new : " + getData); conn.rs = conn.st.executeQuery(getData); while (conn.rs.next()) { HV0308 = HV0309 = HV0310 = HV0311 = HV0312 = HV0320 = HV0321 = HV0322 = HV0323 = HV0324 = 0; HV0314 = HV0315 = HV0316 = HV0317 = HV0318 = HV0334 = HV0335 = HV0336 = HV0337 = HV0338 = 0; currentART1M = currentART1_4M = currentART5_14M = currentART15_19M = currentART20M = 0; currentART1F = currentART1_4F = currentART5_14F = currentART15_19F = currentART20F = 0; newART1M = newART1_4M = newART5_9M = newART10_14M = newART15_19M = newART20_24M = newART25_49M = newART50M = 0; newART1F = newART1_4F = newART5_9F = newART10_14F = newART15_19F = newART20_24F = newART25_49F = newART50F = 0; newCARE1M = newCARE1_4M = newCARE5_9M = newCARE10_14M = newCARE15_19M = newCARE20_24M = newCARE25_49M = newCARE50M = 0; newCARE1F = newCARE1_4F = newCARE5_9F = newCARE10_14F = newCARE15_19F = newCARE20_24F = newCARE25_49F = newCARE50F = 0; currentCARE1M = currentCARE1_4M = currentCARE5_9M = currentCARE10_14M = currentCARE15_19M = currentCARE20_24M = currentCARE25_49M = currentCARE50M = 0; currentCARE1F = currentCARE1_4F = currentCARE5_9F = currentCARE10_14F = currentCARE15_19F = currentCARE20_24F = currentCARE25_49F = currentCARE50F = 0; PMTCT_FO_I_N = PMTCT_FO_I_D = PMTCT_FO_I_LINKED = PMTCT_FO_I_NOT_LINKED = PMTCT_FO_I_UNKNOWN = PMTCT_FO_U_NOT_BREASTFEEDING = PMTCT_FO_U_STILL_BREASTFEEDING = PMTCT_FO_U_BREASTFEEDING_UNKNOWN = PMTCT_FO_OTHER_INCARE = PMTCT_FO_OTHER_NOFOLLOWUP = PMTCT_FO_DIED = PMTCT_FO_TRANSFERRED = 0.0; PMTCT_ARV_N = PMTCT_ARV_D = PMTCT_ARV_LIFELONGART_NEW = PMTCT_ARV_LIFELONGART_EXISTING = PMTCT_ARV_MATERNAL_TRIPLEDRUG_ARV = PMTCT_ARV_MATERNAL_AZT = PMTCT_ARV_SINGLEDOSE = 0.0; PMTCT_EID_N = PMTCT_EID_VIRO_2MONTHS = PMTCT_EID_VIRO_2_12MONTHS = PMTCT_EID_P_VIRO_2MONTHS = PMTCT_EID_P_VIRO_2_12MONTHS = 0.0; PMTCT_STATN_N = PMTCT_STATN_KNOWNPOSTIVE = PMTCTN_STAT_NEWPOSTIVE = 0.0; PMTCT_STATD_D = PMTCT_STATD_LESS15 = PMTCT_STATD_15_19 = PMTCT_STATD_20_24 = PMTCT_STATD_25 = 0.0; PMTCT_CTX = 0.0; facilityName = conn.rs.getString(1); districtName = conn.rs.getString(2); countyName = conn.rs.getString(3); mflcode = conn.rs.getString(4); ARTSupport = conn.rs.getString(5); // CARESuport=conn.rs.getString(6); PMTCTSupport = conn.rs.getString(6); HV0308 = conn.rs.getInt(7); HV0309 = conn.rs.getInt(8); HV0310 = conn.rs.getInt(9); HV0311 = conn.rs.getInt(10); HV0312 = conn.rs.getInt(11); HV0320 = conn.rs.getInt(12); HV0321 = conn.rs.getInt(13); HV0322 = conn.rs.getInt(14); HV0323 = conn.rs.getInt(15); HV0324 = conn.rs.getInt(16); facilityId = conn.rs.getString(17); HV0205 = conn.rs.getInt(18); HV0209 = conn.rs.getInt(19); HV0210 = conn.rs.getInt(20); HV0216 = conn.rs.getInt(21); HV0217 = conn.rs.getInt(22); HV0224 = conn.rs.getInt(23); HV0225 = conn.rs.getInt(24); HV0227 = conn.rs.getInt(25); HV0229 = conn.rs.getInt(26); HV0230 = conn.rs.getInt(27); HV0231 = conn.rs.getInt(28); HV0232 = conn.rs.getInt(29); // HV0301=conn.rs.getInt(30); HV0206 = conn.rs.getInt(31); HV0207 = conn.rs.getInt(32); HV0208 = conn.rs.getInt(33); String getMaxYearMonth = "SELECT MAX(yearmonth) FROM moh731 WHERE moh731.SubPartnerID='" + facilityId + "' && " + duration; conn.rs2 = conn.st2.executeQuery(getMaxYearMonth); if (conn.rs2.next() == true) { maxYearMonth = conn.rs2.getInt(1); } String getCurrent = "SELECT HV0314,HV0315,HV0316,HV0317,HV0318," + "HV0334,HV0335,HV0336,HV0337,HV0338,HV0301 FROM moh731 WHERE " + "moh731.SubPartnerID='" + facilityId + "' && yearmonth='" + maxYearMonth + "'"; System.out.println("current : " + getCurrent); conn.rs1 = conn.st1.executeQuery(getCurrent); if (conn.rs1.next() == true) { HV0314 = conn.rs1.getInt(1); HV0315 = conn.rs1.getInt(2); HV0316 = conn.rs1.getInt(3); HV0317 = conn.rs1.getInt(4); HV0318 = conn.rs1.getInt(5); HV0334 = conn.rs1.getInt(6); HV0335 = conn.rs1.getInt(7); HV0336 = conn.rs1.getInt(8); HV0337 = conn.rs1.getInt(9); HV0338 = conn.rs1.getInt(10); HV0301 = conn.rs1.getInt(11); } if (ARTSupport != null) { double splitData; int adderPos = 0; // VALUES FOR CURRENT ON ART currentART1M = (float) Math.round((0.03 * HV0335)); currentART1_4M = (float) Math.round((0.32 * HV0335)); currentART5_14M = (float) Math.round((0.65 * HV0335)); splitData = currentART1M + currentART1_4M + currentART5_14M; adderPos = 0; while (splitData < HV0335) { if (adderPos < 2) { currentART5_14M += 1; } else { currentART1_4M += 1; } splitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } if (splitData == HV0335) { } } splitData = currentART1M + currentART1_4M + currentART5_14M; adderPos = 0; while (splitData > HV0335) { if (adderPos < 2) { currentART5_14M -= 1; } else { currentART1_4M -= 1; } splitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } if (splitData == HV0335) { } } currentART15_19M = (float) Math.round((0.02 * HV0337)); currentART20M = (float) Math.round((0.98 * HV0337)); splitData = currentART20M + currentART15_19M; while (splitData < HV0337) { currentART20M += 1; splitData++; } splitData = currentART20M + currentART15_19M; while (splitData > HV0337) { currentART20M -= 1; splitData--; } currentART1F = (float) Math.round((0.03 * HV0336));//NEED CLARIFICATION currentART1_4F = (float) Math.round((0.32 * HV0336)); currentART5_14F = (float) Math.round((0.65 * HV0336)); splitData = currentART5_14F + currentART1_4F + currentART1F; adderPos = 0; while (splitData < HV0336) { if (adderPos < 2) { currentART5_14F += 1; } else { currentART1_4F += 1; } splitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } } splitData = currentART5_14F + currentART1_4F + currentART1F; adderPos = 0; while (splitData > HV0336) { if (adderPos < 2) { currentART5_14F -= 1; } else { currentART1_4F -= 1; } splitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } } currentART15_19F = (float) Math.round((0.02 * HV0338)); currentART20F = (float) Math.round((0.98 * HV0338)); splitData = currentART20F + currentART15_19F; while (splitData < HV0338) { currentART20F += 1; splitData++; } splitData = currentART20F + currentART15_19F; while (splitData > HV0338) { currentART20F -= 1; splitData--; } totalCurrentART = HV0338 + HV0336 + HV0337 + HV0335; // VALUES newART1M = (float) Math.round((0.034 * HV0321)); newART1_4M = (float) Math.round((0.214 * HV0321)); newART5_9M = (float) Math.round((0.37 * HV0321)); newART10_14M = (float) Math.round((0.382 * HV0321)); splitData = newART10_14M + newART5_9M + newART1_4M + newART1M; adderPos = 0; while (splitData < HV0321) { if (adderPos == 0) { newART10_14M += 1; } else if (adderPos == 1) { newART5_9M += 1; } else { newART1_4M += 1; } splitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } } splitData = newART10_14M + newART5_9M + newART1_4M + newART1M; adderPos = 0; while (splitData > HV0321) { if (adderPos == 0) { newART10_14M -= 1; } else if (adderPos == 1) { newART5_9M -= 1; } else { newART1_4M -= 1; } splitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } } newART15_19M = (float) Math.round((0.008 * HV0323)); newART20_24M = (float) Math.round((0.008 * HV0323)); newART25_49M = (float) Math.round((0.775 * HV0323)); newART50M = (float) Math.round((0.209 * HV0323)); splitData = newART25_49M + newART50M + newART20_24M + newART15_19M; System.out.println("split data : " + splitData + " all data " + HV0323); adderPos = 0; while (splitData < HV0323) { if (adderPos < 3) { newART25_49M += 1; } else { newART50M += 1; } splitData++; adderPos++; if (adderPos > 3) { adderPos = 0; } } splitData = newART25_49M + newART50M + newART20_24M + newART15_19M; // System.out.println("split data : "+splitData+" all data"+HV0323); adderPos = 0; while (splitData > HV0323) { if (adderPos < 3) { newART25_49M -= 1; } else { newART50M -= 1; } splitData--; adderPos++; if (adderPos > 3) { adderPos = 0; } } newART1F = (float) Math.round((0.034 * HV0322)); newART1_4F = (float) Math.round((0.214 * HV0322)); newART5_9F = (float) Math.round((0.37 * HV0322)); newART10_14F = (float) Math.round((0.382 * HV0322)); splitData = newART10_14F + newART5_9F + newART1_4F + newART1F; adderPos = 0; while (splitData < HV0322) { if (adderPos == 0) { newART10_14F += 1; } else if (adderPos == 1) { newART5_9F += 1; } else { newART1_4F += 1; } splitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } } splitData = newART10_14F + newART5_9F + newART1_4F + newART1F; adderPos = 0; while (splitData > HV0322) { if (adderPos == 0) { newART10_14F -= 1; } else if (adderPos == 1) { newART5_9F -= 1; } else { newART1_4F -= 1; } splitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } } newART15_19F = (float) Math.round((0.008 * HV0324)); newART20_24F = (float) Math.round((0.008 * HV0324)); newART25_49F = (float) Math.round((0.775 * HV0324)); newART50F = (float) Math.round((0.209 * HV0324)); splitData = newART25_49F + newART50F + newART20_24F + newART15_19F; adderPos = 0; while (splitData < HV0324) { if (adderPos < 3) { newART25_49F += 1; } else { newART50F += 1; } splitData++; adderPos++; if (adderPos > 3) { adderPos = 0; } } splitData = newART25_49F + newART50F + newART20_24F + newART15_19F; adderPos = 0; while (splitData > HV0324) { if (adderPos < 3) { newART25_49F -= 1; } else { newART50F -= 1; } splitData--; adderPos++; if (adderPos > 3) { adderPos = 0; } } totalNewART = HV0324 + HV0322 + HV0323 + HV0321; System.out.println(); // VALUES newCARE1M = (float) Math.round((0.18 * HV0309)); newCARE1_4M = (float) Math.round((0.34 * HV0309)); newCARE5_9M = (float) Math.round((0.28 * HV0309)); newCARE10_14M = (float) Math.round((0.20 * HV0309)); splitData = newCARE10_14M + newCARE5_9M + newCARE1_4M + newCARE1M; adderPos = 0; while (splitData < HV0309) { if (adderPos == 0) { newCARE1_4M += 1; } else if (adderPos == 1) { newCARE5_9M += 1; } else if (adderPos == 2) { newCARE10_14M += 1; } else if (adderPos == 3) { newCARE1M += 1; } splitData++; adderPos++; if (adderPos > 3) { adderPos = 0; } } splitData = newCARE10_14M + newCARE5_9M + newCARE1_4M + newCARE1M; adderPos = 0; while (splitData > HV0309) { if (adderPos == 0) { newCARE1_4M -= 1; } else if (adderPos == 1) { newCARE5_9M -= 1; } else if (adderPos == 2) { newCARE10_14M -= 1; } else if (adderPos == 3) { newCARE1M -= 1; } splitData--; adderPos++; if (adderPos > 3) { adderPos = 0; } } newCARE15_19M = (float) Math.round((0.02 * HV0311)); newCARE20_24M = (float) Math.round((0.09 * HV0311)); newCARE25_49M = (float) Math.round((0.80 * HV0311)); newCARE50M = (float) Math.round((0.09 * HV0311)); splitData = newCARE50M + newCARE25_49M + newCARE20_24M + newCARE15_19M; while (splitData < HV0311) { newCARE25_49M += 1; splitData++; } splitData = newCARE50M + newCARE25_49M + newCARE20_24M + newCARE15_19M; while (splitData > HV0311) { newCARE25_49M -= 1; splitData--; } newCARE1F = (float) Math.round((0.18 * HV0310)); newCARE1_4F = (float) Math.round((0.34 * HV0310)); newCARE5_9F = (float) Math.round((0.28 * HV0310)); newCARE10_14F = (float) Math.round((0.20 * HV0310)); splitData = newCARE10_14F + newCARE5_9F + newCARE1_4F + newCARE1F; adderPos = 0; while (splitData < HV0310) { if (adderPos == 0) { newCARE1_4F += 1; } else if (adderPos == 1) { newCARE5_9F += 1; } else if (adderPos == 2) { newCARE10_14F += 1; } else if (adderPos == 3) { newCARE1F += 1; } splitData++; adderPos++; if (adderPos > 3) { adderPos = 0; } } splitData = newCARE10_14F + newCARE5_9F + newCARE1_4F + newCARE1F; adderPos = 0; while (splitData > HV0310) { if (adderPos == 0) { newCARE1_4F -= 1; } else if (adderPos == 1) { newCARE5_9F -= 1; } else if (adderPos == 2) { newCARE10_14F -= 1; } else if (adderPos == 3) { newCARE1F -= 1; } splitData--; adderPos++; if (adderPos > 3) { adderPos = 0; } } newCARE15_19F = (float) Math.round((0.02 * HV0312)); newCARE20_24F = (float) Math.round((0.09 * HV0312)); newCARE25_49F = (float) Math.round((0.80 * HV0312)); newCARE50F = (float) Math.round((0.09 * HV0312)); splitData = newCARE50F + newCARE25_49F + newCARE20_24F + newCARE15_19F; while (splitData < HV0312) { newCARE25_49F += 1; splitData++; } splitData = newCARE50F + newCARE25_49F + newCARE20_24F + newCARE15_19F; while (splitData > HV0312) { newCARE25_49F -= 1; splitData--; } totalNewCARE = HV0312 + HV0310 + HV0311 + HV0309; // VALUES currentCARE1M = (float) Math.round((0.03 * HV0315)); currentCARE1_4M = (float) Math.round((0.22 * HV0315)); currentCARE5_9M = (float) Math.round((0.37 * HV0315)); currentCARE10_14M = (float) Math.round((0.38 * HV0315)); splitData = currentCARE10_14M + currentCARE5_9M + currentCARE1_4M + currentCARE1M; adderPos = 0; while (splitData < HV0315) { if (adderPos == 0) { currentCARE10_14M += 1; } else if (adderPos == 1) { currentCARE5_9M += 1; } else if (adderPos == 2) { currentCARE1_4M += 1; } splitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } } splitData = currentCARE10_14M + currentCARE5_9M + currentCARE1_4M + currentCARE1M; adderPos = 0; while (splitData > HV0315) { if (adderPos == 0) { currentCARE10_14M -= 1; } else if (adderPos == 1) { currentCARE5_9M -= 1; } else if (adderPos == 2) { currentCARE1_4M -= 1; } splitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } } currentCARE15_19M = (float) Math.round((0.02 * HV0317)); currentCARE20_24M = (float) Math.round((0.09 * HV0317)); currentCARE25_49M = (float) Math.round((0.80 * HV0317)); currentCARE50M = (float) Math.round((0.09 * HV0317)); splitData = currentCARE50M + currentCARE25_49M + currentCARE20_24M + currentCARE15_19M; while (splitData < HV0317) { currentCARE25_49M += 1; splitData++; } splitData = currentCARE50M + currentCARE25_49M + currentCARE20_24M + currentCARE15_19M; while (splitData > HV0317) { currentCARE25_49M -= 1; splitData--; } currentCARE1F = (float) Math.round((0.03 * HV0316)); currentCARE1_4F = (float) Math.round((0.22 * HV0316)); currentCARE5_9F = (float) Math.round((0.37 * HV0316)); currentCARE10_14F = (float) Math.round((0.38 * HV0316)); splitData = currentCARE10_14F + currentCARE5_9F + currentCARE1_4F + currentCARE1F; adderPos = 0; while (splitData < HV0316) { if (adderPos == 0) { currentCARE10_14F += 1; } else if (adderPos == 1) { currentCARE5_9F += 1; } else if (adderPos == 2) { currentCARE1_4F += 1; } splitData++; adderPos++; if (adderPos > 2) { adderPos = 0; } } splitData = currentCARE10_14F + currentCARE5_9F + currentCARE1_4F + currentCARE1F; adderPos = 0; while (splitData > HV0316) { if (adderPos == 0) { currentCARE10_14F -= 1; } else if (adderPos == 1) { currentCARE5_9F -= 1; } else if (adderPos == 2) { currentCARE1_4F -= 1; } splitData--; adderPos++; if (adderPos > 2) { adderPos = 0; } } currentCARE15_19F = (float) Math.round((0.02 * HV0318)); currentCARE20_24F = (float) Math.round((0.09 * HV0318)); currentCARE25_49F = (float) Math.round((0.80 * HV0318)); currentCARE50F = (float) Math.round((0.09 * HV0318)); splitData = currentCARE50F + currentCARE25_49F + currentCARE20_24F + currentCARE15_19F; while (splitData < HV0318) { currentCARE25_49F += 1; splitData++; } splitData = currentCARE50F + currentCARE25_49F + currentCARE20_24F + currentCARE15_19F; while (splitData > HV0318) { currentCARE25_49F -= 1; splitData--; } totalCurrentCARE = HV0318 + HV0316 + HV0317 + HV0315; String dataART[] = (countyName + "," + districtName + "," + facilityName + "," + mflcode + "," + ARTSupport + "," + totalCurrentART + "," + "" + currentART1F + "," + currentART1_4F + "," + currentART5_14F + "," + currentART15_19F + "," + "" + currentART20F + "," + currentART1M + "," + currentART1_4M + "," + currentART5_14M + "," + "" + currentART15_19M + "," + currentART20M + "," + totalNewART + "," + "" + newART1F + "," + newART1_4F + "," + newART5_9F + "," + newART10_14F + "," + newART15_19F + "," + newART20_24F + "," + "" + newART25_49F + "," + newART50F + "," + newART1M + "," + newART1_4M + "," + newART5_9M + "," + newART10_14M + "," + "" + newART15_19M + "," + newART20_24M + "," + newART25_49M + "," + newART50M).split(","); String dataCARE[] = (countyName + "," + districtName + "," + facilityName + "," + mflcode + "," + ARTSupport + "," + totalCurrentCARE + "," + "" + currentCARE1F + "," + currentCARE1_4F + "," + currentCARE5_9F + "," + currentCARE10_14F + "," + currentCARE15_19F + "," + "" + currentCARE20_24F + "," + currentCARE25_49F + "," + currentCARE50F + "," + currentCARE1M + "," + currentCARE1_4M + "," + "" + currentCARE5_9M + "," + currentCARE10_14M + "," + currentCARE15_19M + "," + currentCARE20_24M + "," + "" + currentCARE25_49M + "," + currentCARE50M + "," + totalNewCARE + "," + "" + newCARE1F + "," + newCARE1_4F + "," + newCARE5_9F + "," + newCARE10_14F + "," + newCARE15_19F + "," + newCARE20_24F + "," + "" + newCARE25_49F + "," + newCARE50F + "," + newCARE1M + "," + newCARE1_4M + "," + newCARE5_9M + "," + newCARE10_14M + "," + "" + newCARE15_19M + "," + newCARE20_24M + "," + newCARE25_49M + "," + newCARE50M).split(","); artpos++; HSSFRow rw3shet1 = shet1.createRow(artpos); rw3shet1.setHeightInPoints(25); for (int positionART = 0; positionART < dataART.length; positionART++) { String value = dataART[positionART]; c11 = rw3shet1.createCell(positionART); if (positionART > 4) { c11.setCellValue(Double.parseDouble(value)); } else { c11.setCellValue(value); } c11.setCellStyle(stborder); if (positionART == 5 || positionART == 16) { c11.setCellStyle(styleHeader); } } // System.out.println("art data length : "+dataART.length); HSSFRow rw3Shet2 = shet2.createRow(artpos); rw3Shet2.setHeightInPoints(25); for (int positionCARE = 0; positionCARE < dataCARE.length; positionCARE++) { String value = dataCARE[positionCARE]; c11 = rw3Shet2.createCell(positionCARE); if (positionCARE > 4) { c11.setCellValue(Double.parseDouble(value)); } else { c11.setCellValue(value); } c11.setCellStyle(stborder); if (positionCARE == 5 || positionCARE == 22) { c11.setCellStyle(styleHeader); } } } // OUTPUT PMTCT DATA HERE +=========================================================================================== if (PMTCTSupport != null) { numerator = denominator = 0; String pmtct_fo_id = year + "_" + quarter + "_" + facilityId; String getPMTCTFO = "SELECT numerator,denominator FROM pmtct_fo WHERE id='" + pmtct_fo_id + "' "; conn.rs1 = conn.st1.executeQuery(getPMTCTFO); if (conn.rs1.next() == true) { numerator = conn.rs1.getInt(1); denominator = conn.rs1.getInt(2); } if (!reportDuration.equals("3")) { numerator = denominator = 0; } // PMTCT_FO=================================================================================================== PMTCT_FO_I_N = (double) numerator; PMTCT_FO_I_D = (double) denominator; PMTCT_FO_I_LINKED = (double) Math.round((0.05 * numerator)); PMTCT_FO_I_NOT_LINKED = 0.0; PMTCT_FO_I_UNKNOWN = 0.0; PMTCT_FO_U_NOT_BREASTFEEDING = (double) Math.round((0.83 * numerator)); PMTCT_FO_U_STILL_BREASTFEEDING = 0.0; PMTCT_FO_U_BREASTFEEDING_UNKNOWN = 0.0; PMTCT_FO_OTHER_INCARE = 0.0; PMTCT_FO_OTHER_NOFOLLOWUP = (double) Math.round((0.08 * numerator)); PMTCT_FO_DIED = (double) Math.round((0.02 * numerator)); PMTCT_FO_TRANSFERRED = (double) Math.round((0.02 * numerator)); double normalizer = PMTCT_FO_I_LINKED + PMTCT_FO_I_NOT_LINKED + PMTCT_FO_I_UNKNOWN + PMTCT_FO_U_NOT_BREASTFEEDING + PMTCT_FO_U_STILL_BREASTFEEDING + PMTCT_FO_U_BREASTFEEDING_UNKNOWN + PMTCT_FO_OTHER_INCARE + PMTCT_FO_OTHER_NOFOLLOWUP + PMTCT_FO_DIED + PMTCT_FO_TRANSFERRED; int pmtctnum = 0; while (numerator > normalizer) { PMTCT_FO_U_NOT_BREASTFEEDING++; normalizer++; } while (numerator < normalizer) { PMTCT_FO_U_NOT_BREASTFEEDING--; normalizer--; } // PMTCT_ARV=================================================================================================== PMTCT_ARV_N = (double) HV0217; PMTCT_ARV_D = (double) HV0209; PMTCT_ARV_LIFELONGART_NEW = (double) Math.round((0.75 * HV0217)); PMTCT_ARV_LIFELONGART_EXISTING = (double) Math.round((0.25 * HV0217)); normalizer = PMTCT_ARV_LIFELONGART_NEW + PMTCT_ARV_LIFELONGART_EXISTING; pmtctnum = 0; while (HV0217 > normalizer) { if (pmtctnum < 3) { PMTCT_ARV_LIFELONGART_NEW++; } else { PMTCT_ARV_LIFELONGART_EXISTING++; } if (pmtctnum == 3) { pmtctnum = 0; } normalizer++; pmtctnum++; } normalizer = PMTCT_ARV_LIFELONGART_NEW + PMTCT_ARV_LIFELONGART_EXISTING; pmtctnum = 0; while (normalizer > HV0217) { if (pmtctnum < 3) { PMTCT_ARV_LIFELONGART_NEW--; } else { PMTCT_ARV_LIFELONGART_EXISTING--; } if (pmtctnum == 3) { pmtctnum = 0; } normalizer--; pmtctnum++; } PMTCT_ARV_MATERNAL_TRIPLEDRUG_ARV = 0.0; PMTCT_ARV_MATERNAL_AZT = 0.0; PMTCT_ARV_SINGLEDOSE = 0.0; // PMTCT_EID=================================================================================================== PMTCT_EID_N = (double) (HV0224 + HV0225 + HV0227); PMTCT_EID_VIRO_2MONTHS = (double) (HV0224); PMTCT_EID_VIRO_2_12MONTHS = (double) (HV0225 + HV0227); PMTCT_EID_P_VIRO_2MONTHS = (double) (HV0229); PMTCT_EID_P_VIRO_2_12MONTHS = (double) (HV0230 + HV0231); // PMTCT_STAT NUMERATOR====================================================================================================== PMTCT_STATN_N = (double) HV0210; PMTCT_STATN_KNOWNPOSTIVE = (double) HV0205; PMTCTN_STAT_NEWPOSTIVE = (double) (HV0206 + HV0207 + HV0208); PMTCT_STATD_D = (double) Math.round((1.03 * HV0210)); // PMTCT_CTX===================================================================================================== PMTCT_CTX = (double) HV0301; //Mo clarification whether to um or take most recent if (PMTCTSupport != null) { if (!reportDuration.equals("3")) { numerator = denominator = 0; String dataPMTCT[] = (countyName + "," + districtName + "," + facilityName + "," + mflcode + "," + PMTCTSupport + ",," + ",,,,,,,,,,," + "" + PMTCT_ARV_N + "," + PMTCT_ARV_D + "," + PMTCT_ARV_LIFELONGART_NEW + "," + PMTCT_ARV_LIFELONGART_EXISTING + "," + PMTCT_ARV_MATERNAL_TRIPLEDRUG_ARV + "," + PMTCT_ARV_MATERNAL_AZT + "," + "" + PMTCT_ARV_SINGLEDOSE + "," + PMTCT_EID_N + "," + PMTCT_EID_VIRO_2MONTHS + "," + PMTCT_EID_VIRO_2_12MONTHS + "," + PMTCT_EID_P_VIRO_2MONTHS + "," + PMTCT_EID_P_VIRO_2_12MONTHS + "," + "" + PMTCT_STATN_N + "," + PMTCT_STATN_KNOWNPOSTIVE + "," + PMTCTN_STAT_NEWPOSTIVE + "," + "" + PMTCT_STATD_D + "," + PMTCT_CTX).split(","); HSSFRow rw3shetPMTCT = shetPMTCT.createRow(pmtctpos); rw3shetPMTCT.setHeightInPoints(25); for (int positionPMTCT = 0; positionPMTCT < dataPMTCT.length; positionPMTCT++) { String value = dataPMTCT[positionPMTCT]; c11 = rw3shetPMTCT.createCell(positionPMTCT); if (positionPMTCT > 16 && positionPMTCT < (dataPMTCT.length)) { c11.setCellValue(Double.parseDouble(value)); } else { c11.setCellValue(value); } c11.setCellStyle(stborder); if (positionPMTCT == 17 || positionPMTCT == 18 || positionPMTCT == 24 || positionPMTCT == 29 || positionPMTCT == 32 || positionPMTCT == 33) { c11.setCellStyle(styleHeader); } } pmtctpos++; } else { // HAVE FORMULAS HERE AND THE OUTPUT FOR PMTCT String dataPMTCT[] = (countyName + "," + districtName + "," + facilityName + "," + mflcode + "," + PMTCTSupport + "," + PMTCT_FO_I_N + "," + "" + PMTCT_FO_I_D + "," + PMTCT_FO_I_LINKED + "," + PMTCT_FO_I_NOT_LINKED + "," + PMTCT_FO_I_UNKNOWN + "," + "" + PMTCT_FO_U_NOT_BREASTFEEDING + "," + PMTCT_FO_U_STILL_BREASTFEEDING + "," + PMTCT_FO_U_BREASTFEEDING_UNKNOWN + "," + PMTCT_FO_OTHER_INCARE + "," + PMTCT_FO_OTHER_NOFOLLOWUP + "," + PMTCT_FO_DIED + "," + PMTCT_FO_TRANSFERRED + "," + "" + PMTCT_ARV_N + "," + PMTCT_ARV_D + "," + PMTCT_ARV_LIFELONGART_NEW + "," + PMTCT_ARV_LIFELONGART_EXISTING + "," + PMTCT_ARV_MATERNAL_TRIPLEDRUG_ARV + "," + PMTCT_ARV_MATERNAL_AZT + "," + "" + PMTCT_ARV_SINGLEDOSE + "," + PMTCT_EID_N + "," + PMTCT_EID_VIRO_2MONTHS + "," + PMTCT_EID_VIRO_2_12MONTHS + "," + PMTCT_EID_P_VIRO_2MONTHS + "," + PMTCT_EID_P_VIRO_2_12MONTHS + "," + "" + PMTCT_STATN_N + "," + PMTCT_STATN_KNOWNPOSTIVE + "," + PMTCTN_STAT_NEWPOSTIVE + "," + "" + PMTCT_STATD_D + ",").split(","); HSSFRow rw3shetPMTCT = shetPMTCT.createRow(pmtctpos); rw3shetPMTCT.setHeightInPoints(25); for (int positionPMTCT = 0; positionPMTCT < dataPMTCT.length; positionPMTCT++) { String value = dataPMTCT[positionPMTCT]; c11 = rw3shetPMTCT.createCell(positionPMTCT); if (positionPMTCT > 4 && positionPMTCT < (dataPMTCT.length)) { c11.setCellValue(Double.parseDouble(value)); } else { c11.setCellValue(value); } c11.setCellStyle(stborder); if (positionPMTCT == 5 || positionPMTCT == 6 || positionPMTCT == 17 || positionPMTCT == 18 || positionPMTCT == 24 || positionPMTCT == 29 || positionPMTCT == 32 || positionPMTCT == 33) { c11.setCellStyle(styleHeader); } } pmtctpos++; } } } } // 711 REPORT // MALES int TestedAdultMale = 0, TestedAdultFemale = 0; int TestedChildMale = 0, TestedChildFemale = 0; int HIV_AdultMale = 0, HIV_AdultFemale = 0; int HIV_ChildMale = 0, HIV_ChildFemale = 0; double FemaleAdultTested; double FemaleTestedChild; double AdultFemaleHIV; double ChildFemaleHIV; double MaleAdultTested; double MaleTestedChild; double AdultMaleHIV; double ChildMaleHIV; double FemaleAdultTested1 = 0; double FemaleAdultTested4 = 0; double FemaleAdultTested9 = 0; double FemaleAdultTested14 = 0; double FemaleAdultTested19 = 0; double FemaleAdultTested24 = 0; double FemaleAdultTested49 = 0; double FemaleAdultTested50 = 0; double FemaleTestedChild1 = 0; double FemaleTestedChild4 = 0; double FemaleTestedChild9 = 0; double FemaleTestedChild14 = 0; double FemaleTestedChild19 = 0; double FemaleTestedChild24 = 0; double FemaleTestedChild49 = 0; double FemaleTestedChild50 = 0; double AdultFemaleHIV19Neg = 0; double AdultFemaleHIV24Neg = 0; double AdultFemaleHIV49Neg = 0; double AdultFemaleHIV50Neg = 0; double AdultFemaleHIV19 = 0; double AdultFemaleHIV24 = 0; double AdultFemaleHIV49 = 0; double AdultFemaleHIV50 = 0; double ChildFemaleHIV1 = 0; double ChildFemaleHIV4 = 0; double ChildFemaleHIV9 = 0; double ChildFemaleHIV14 = 0; double ChildFemaleHIV1Neg = 0; double ChildFemaleHIV4Neg = 0; double ChildFemaleHIV9Neg = 0; double ChildFemaleHIV14Neg = 0; // MALES double MaleAdultTested19Neg = 0; double MaleAdultTested21Neg = 0; double MaleAdultTested49Neg = 0; double MaleAdultTested50Neg = 0; double MaleAdultTested19 = 0; double MaleAdultTested24 = 0; double MaleAdultTested49 = 0; double MaleAdultTested50 = 0; double MaleTestedChild1 = 0; double MaleTestedChild4 = 0; double MaleTestedChild9 = 0; double MaleTestedChild14 = 0; double MaleTestedChild1Neg = 0; double MaleTestedChild4Neg = 0; double MaleTestedChild9Neg = 0; double MaleTestedChild14Neg = 0; double AdultMaleHIV19Neg = 0; double AdultMaleHIV24Neg = 0; double AdultMaleHIV49Neg = 0; double AdultMaleHIV50Neg = 0; double AdultMaleHIV19 = 0; double AdultMaleHIV24 = 0; double AdultMaleHIV49 = 0; double AdultMaleHIV50 = 0; double ChildMaleHIV1 = 0; double ChildMaleHIV4 = 0; double ChildMaleHIV9 = 0; double ChildMaleHIV14 = 0; double ChildMaleHIV1Neg = 0; double ChildMaleHIV4Neg = 0; double ChildMaleHIV9Neg = 0; double ChildMaleHIV14Neg = 0; double splitData = 0; int adderPos = 0; double childSplitData = 0; year = Integer.parseInt(request.getParameter("year")); reportDuration = request.getParameter("reportDuration"); // year=2015; // reportDuration="4"; String period1 = ""; String duration1 = ""; prevYear = year - 1; maxYearMonth = 0; // GET REPORT DURATION============================================ if (reportDuration.equals("1")) { duration1 = " moh711.yearmonth BETWEEN " + prevYear + "10 AND " + year + "09"; period1 = "DATIM ANNUAL DATA REPORT FOR PEPFAR YEAR : " + year; } else if (reportDuration.equals("2")) { semi_annual = request.getParameter("semi_annual"); // semi_annual="2"; if (semi_annual.equals("1")) { duration1 = " moh711.yearmonth BETWEEN " + prevYear + "10 AND " + year + "03"; period1 = "DATIM SEMI - ANNUAL DATA REPORT FOR PERIOD : OCT " + prevYear + " to MARCH " + year; } else { duration1 = " moh711.yearmonth BETWEEN " + year + "04 AND " + year + "09"; period1 = "DATIM SEMI - ANNUAL DATA REPORT FOR PERIOD : APRIL " + year + " to SEPT " + year; } } else if (reportDuration.equals("3")) { String startMonth, endMonth; quarter = request.getParameter("quarter"); // quarter="3"; String getMonths = "SELECT months,name FROM quarter WHERE id='" + quarter + "'"; conn.rs = conn.st.executeQuery(getMonths); if (conn.rs.next() == true) { String months[] = conn.rs.getString(1).split(","); startMonth = months[0]; endMonth = months[2]; if (quarter.equals("1")) { duration1 = " moh711.yearmonth BETWEEN " + prevYear + "" + startMonth + " AND " + prevYear + "" + endMonth; period1 = "DATIM QUARTERLY DATA REPORT FOR PERIOD : " + conn.rs.getString(2).replace("-", " " + prevYear + " TO ") + " " + prevYear + ""; } else { duration1 = " moh711.yearmonth BETWEEN " + year + "" + startMonth + " AND " + year + "" + endMonth; period1 = "DATIM QUARTERLY DATA REPORT FOR PERIOD : " + conn.rs.getString(2).replace("-", " " + year + " TO ") + " " + year + ""; } } } else if (reportDuration.equals("4")) { month = Integer.parseInt(request.getParameter("month")); // month=5; String getMonthName = "SELECT name FROM month WHERE id='" + month + "'"; conn.rs = conn.st.executeQuery(getMonthName); if (conn.rs.next() == true) { if (month >= 10) { duration1 = " moh711.yearmonth=" + prevYear + "" + month; period1 = "DATIM MONTHLY DATA REPORT FOR : " + conn.rs.getString(1) + "(" + prevYear + ")"; } else { duration1 = " moh711.yearmonth=" + year + "0" + month; period1 = "DATIM MONTHLY DATA REPORT FOR : " + conn.rs.getString(1) + "(" + year + ")"; } } } else { duration1 = ""; } FemaleAdultTested = 0; FemaleTestedChild = 0; AdultFemaleHIV = 0; ChildFemaleHIV = 0; double TotalTested = 0; double TotalPositiveFemale = 0; double TotalPositiveMale = 0; double TotalNegativeFemale = 0; double TotalNegativeMale = 0; // MALES MaleAdultTested = 0; MaleTestedChild = 0; AdultMaleHIV = 0; ChildMaleHIV = 0; double TotalPositive = 0; double TotalNegative = 0; String county = ""; String district = ""; String facilityname = ""; HSSFCellStyle stylex = wb.createCellStyle(); stylex.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); stylex.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylex.setBorderTop(HSSFCellStyle.BORDER_THIN); stylex.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylex.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylex.setBorderRight(HSSFCellStyle.BORDER_THIN); stylex.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont fontx = wb.createFont(); fontx.setColor(HSSFColor.DARK_BLUE.index); stylex.setFont(fontx); stylex.setWrapText(true); HSSFCellStyle stylemainHeader = wb.createCellStyle(); stylemainHeader.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); stylemainHeader.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); stylemainHeader.setBorderTop(HSSFCellStyle.BORDER_THIN); stylemainHeader.setBorderBottom(HSSFCellStyle.BORDER_THIN); stylemainHeader.setBorderLeft(HSSFCellStyle.BORDER_THIN); stylemainHeader.setBorderRight(HSSFCellStyle.BORDER_THIN); stylemainHeader.setAlignment(HSSFCellStyle.ALIGN_CENTER); stylemainHeader.setWrapText(true); shet3.setColumnWidth(0, 4000); shet3.setColumnWidth(1, 5000); shet3.setColumnWidth(2, 7000); shet3.setColumnWidth(6, 5000); // for (int i=1;i<=42;i++){ // shet3.setColumnWidth(i, 2000); // } HSSFRow rw0 = shet3.createRow(0); rw0.setHeightInPoints(30); HSSFCell c1, c2, c3, c4, c5, c6, c7, c8; c1 = rw0.createCell(0); c1.setCellValue(period); c1.setCellStyle(stylemainHeader); for (int j = 1; j <= 43; j++) { c1 = rw0.createCell(j); c1.setCellStyle(stylemainHeader); } shet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 17)); HSSFCell c12, c13, c14, c15, c16, c17, c18, c19, c20, c110, c111, c112, c113, c114, c115, c116, c117, c118, c219; HSSFCell c119, c120, c121, c122, c123, c124, c125, c126, c127, c128, c129, c130, c131, c132, c133, c134, c135, c136, c137; HSSFCell c211, c212, c213, c214, c215, c216, c217; rw0 = shet3.createRow(2); rw0.setHeightInPoints(30); c211 = rw0.createCell(0); c212 = rw0.createCell(1); c213 = rw0.createCell(2); c214 = rw0.createCell(3); c215 = rw0.createCell(4); c216 = rw0.createCell(5); c217 = rw0.createCell(6); c211.setCellValue("COUNTY"); c212.setCellValue("SUB-COUNTY"); c213.setCellValue("FACILITY"); c214.setCellValue("MFL-CODE"); c215.setCellValue("TYPE OF SUPPORT"); c11 = rw0.createCell(7); c12 = rw0.createCell(8); c13 = rw0.createCell(9); c14 = rw0.createCell(10); c15 = rw0.createCell(11); c16 = rw0.createCell(12); c17 = rw0.createCell(13); c18 = rw0.createCell(14); c19 = rw0.createCell(15); c110 = rw0.createCell(16); c111 = rw0.createCell(17); c112 = rw0.createCell(18); c113 = rw0.createCell(19); c114 = rw0.createCell(20); c115 = rw0.createCell(21); c116 = rw0.createCell(22); c117 = rw0.createCell(23); c118 = rw0.createCell(24); c119 = rw0.createCell(25); c120 = rw0.createCell(26); c121 = rw0.createCell(27); c122 = rw0.createCell(28); c123 = rw0.createCell(29); c124 = rw0.createCell(30); c125 = rw0.createCell(31); c126 = rw0.createCell(32); c127 = rw0.createCell(33); c128 = rw0.createCell(34); c129 = rw0.createCell(35); c130 = rw0.createCell(36); c131 = rw0.createCell(37); c132 = rw0.createCell(38); c133 = rw0.createCell(39); c134 = rw0.createCell(40); c135 = rw0.createCell(41); c136 = rw0.createCell(42); c137 = rw0.createCell(43); c216.setCellValue("Total HIV+"); c217.setCellValue("Total HIV+ (F)"); c11.setCellValue("POSITIVE"); c12.setCellValue("FEMALE"); c216.setCellStyle(stylemainHeader); c217.setCellStyle(stylemainHeader); c11.setCellStyle(stylemainHeader); c12.setCellStyle(stylemainHeader); c13.setCellValue(""); c14.setCellValue(""); c15.setCellValue(""); c16.setCellValue(""); c17.setCellValue(""); c18.setCellValue(""); c19.setCellValue(""); c110.setCellValue(""); c111.setCellValue("MALE"); c111.setCellStyle(stylemainHeader); c112.setCellValue(""); c113.setCellValue(""); c114.setCellValue(""); c115.setCellValue(""); c116.setCellValue(""); c117.setCellValue(""); c118.setCellValue(""); // // rw0=shet3.createRow(2); // rw0.setHeightInPoints(20); c119.setCellValue("NEGATIVE"); c120.setCellValue("FEMALE"); c119.setCellStyle(stylemainHeader); c120.setCellStyle(stylemainHeader); c121.setCellValue(""); c122.setCellValue(""); c123.setCellValue(""); c124.setCellValue(""); c125.setCellValue(""); c126.setCellValue(""); c127.setCellValue(""); c128.setCellValue(""); c129.setCellValue("MALE"); c129.setCellStyle(stylemainHeader); c130.setCellValue(""); c131.setCellValue(""); c132.setCellValue(""); c133.setCellValue(""); c134.setCellValue(""); c135.setCellValue(""); c136.setCellValue(""); c137.setCellValue(""); for (int i = 0; i <= 22; i++) { c11 = rw0.getCell(i); c11.setCellStyle(stylemainHeader); } for (int i = 23; i <= 43; i++) { c11 = rw0.getCell(i); c11.setCellStyle(stylemainHeader); } shet3.addMergedRegion(new CellRangeAddress(2, 2, 8, 15)); shet3.addMergedRegion(new CellRangeAddress(2, 2, 17, 24)); shet3.addMergedRegion(new CellRangeAddress(2, 2, 26, 33)); shet3.addMergedRegion(new CellRangeAddress(2, 2, 35, 42)); rw0 = shet3.createRow(3); rw0.setHeightInPoints(30); c211 = rw0.createCell(0); c212 = rw0.createCell(1); c213 = rw0.createCell(2); c214 = rw0.createCell(3); c215 = rw0.createCell(4); c216 = rw0.createCell(5); c217 = rw0.createCell(6); // other data c11 = rw0.createCell(7); c12 = rw0.createCell(8); c13 = rw0.createCell(9); c14 = rw0.createCell(10); c15 = rw0.createCell(11); c16 = rw0.createCell(12); c17 = rw0.createCell(13); c18 = rw0.createCell(14); c19 = rw0.createCell(15); c110 = rw0.createCell(16); c111 = rw0.createCell(17); c112 = rw0.createCell(18); c113 = rw0.createCell(19); c114 = rw0.createCell(20); c115 = rw0.createCell(21); c116 = rw0.createCell(22); c117 = rw0.createCell(23); c118 = rw0.createCell(24); c11.setCellValue("Num"); c216.setCellValue("TOTAL HIV+"); c217.setCellValue("TOTAL +VE (F)"); c12.setCellValue("Paeds <15Yr"); c13.setCellValue(""); c14.setCellValue(""); c15.setCellValue(""); c16.setCellValue("Adults 15+Yr"); c17.setCellValue(""); c18.setCellValue(""); c19.setCellValue(""); c110.setCellValue("TOTAL +VE MALE"); c111.setCellValue("Paeds <15Yr"); c112.setCellValue(""); c113.setCellValue(""); c114.setCellValue(""); c115.setCellValue("Adults 15+Yr"); c116.setCellValue(""); c117.setCellValue(""); c118.setCellValue(""); c11 = rw0.createCell(25); c12 = rw0.createCell(26); c13 = rw0.createCell(27); c14 = rw0.createCell(28); c15 = rw0.createCell(29); c16 = rw0.createCell(30); c17 = rw0.createCell(31); c18 = rw0.createCell(32); c19 = rw0.createCell(33); c110 = rw0.createCell(34); c111 = rw0.createCell(35); c112 = rw0.createCell(36); c113 = rw0.createCell(37); c114 = rw0.createCell(38); c115 = rw0.createCell(39); c116 = rw0.createCell(40); c117 = rw0.createCell(41); c118 = rw0.createCell(42); c119 = rw0.createCell(43); c11.setCellValue("TOTAL -VE(F)"); c12.setCellValue("Paeds <15Yr"); c13.setCellValue(""); c14.setCellValue(""); c15.setCellValue(""); c16.setCellValue("Adults 15+Yr"); c17.setCellValue(""); c18.setCellValue(""); c19.setCellValue(""); c110.setCellValue("TOTAL -VE(M)"); c111.setCellValue("Paeds <15Yr"); c112.setCellValue(""); c113.setCellValue(""); c114.setCellValue(""); c115.setCellValue("Adults 15+Yr"); c116.setCellValue(""); c117.setCellValue(""); c118.setCellValue(""); c119.setCellValue("DQA"); for (int i = 0; i <= 22; i++) { c11 = rw0.getCell(i); c11.setCellStyle(styleHeader); } shet3.addMergedRegion(new CellRangeAddress(3, 3, 8, 11)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 12, 15)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 17, 20)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 21, 24)); // for (int k = 23; k <= 43; k++) { c113 = rw0.getCell(k); c113.setCellStyle(styleHeader); } shet3.addMergedRegion(new CellRangeAddress(3, 3, 26, 29)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 30, 33)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 35, 38)); shet3.addMergedRegion(new CellRangeAddress(3, 3, 39, 42)); shet3.addMergedRegion(new CellRangeAddress(3, 4, 43, 43)); rw0 = shet3.createRow(4); rw0.setHeightInPoints(30); c211 = rw0.createCell(0); c212 = rw0.createCell(1); c213 = rw0.createCell(2); c214 = rw0.createCell(3); c215 = rw0.createCell(4); c216 = rw0.createCell(5); c217 = rw0.createCell(6); // for ther est c11 = rw0.createCell(7); c12 = rw0.createCell(8); c13 = rw0.createCell(9); c14 = rw0.createCell(10); c15 = rw0.createCell(11); c16 = rw0.createCell(12); c17 = rw0.createCell(13); c18 = rw0.createCell(14); c19 = rw0.createCell(15); c110 = rw0.createCell(16); c111 = rw0.createCell(17); c112 = rw0.createCell(18); c113 = rw0.createCell(19); c114 = rw0.createCell(20); c115 = rw0.createCell(21); c116 = rw0.createCell(22); c117 = rw0.createCell(23); c118 = rw0.createCell(24); c11.setCellValue("NUM"); c216.setCellValue("TOTAL HIV+"); c217.setCellValue("TOTAL +VE(F)"); c12.setCellValue("<1 "); c13.setCellValue("1-4Y"); c14.setCellValue("5-9Y"); c15.setCellValue("10-14Y"); c16.setCellValue("15-19Y"); c17.setCellValue("20-24Y"); c18.setCellValue("25-49Y"); c19.setCellValue("50+Y"); c110.setCellValue("TOTAL +VE MALE"); shet3.addMergedRegion(new CellRangeAddress(3, 4, 16, 16)); c111.setCellValue("<1"); c112.setCellValue("1-4Y"); c113.setCellValue("5-9Y"); c114.setCellValue("10-14Y"); c115.setCellValue("15-19Y"); c116.setCellValue("20-24Y"); c117.setCellValue("25-49Y"); c118.setCellValue("50+Y"); for (int i = 0; i <= 22; i++) { c11 = rw0.getCell(i); c11.setCellStyle(styleHeader); } // // for negative c11 = rw0.createCell(25); c12 = rw0.createCell(26); c13 = rw0.createCell(27); c14 = rw0.createCell(28); c15 = rw0.createCell(29); c16 = rw0.createCell(30); c17 = rw0.createCell(31); c18 = rw0.createCell(32); c19 = rw0.createCell(33); c110 = rw0.createCell(34); c111 = rw0.createCell(35); c112 = rw0.createCell(36); c113 = rw0.createCell(37); c114 = rw0.createCell(38); c115 = rw0.createCell(39); c116 = rw0.createCell(40); c117 = rw0.createCell(41); c118 = rw0.createCell(42); c119 = rw0.createCell(43); c11.setCellValue("TOTAL -VE(F)"); shet3.addMergedRegion(new CellRangeAddress(3, 4, 25, 25)); c12.setCellValue("<1"); c13.setCellValue("1-4Y"); c14.setCellValue("5-9Y"); c15.setCellValue("10-14Y"); c16.setCellValue("15-19Y"); c17.setCellValue("20-24Y"); c18.setCellValue("25-49Y"); c19.setCellValue("50+Y"); c110.setCellValue("TOTAL -VE(M)"); shet3.addMergedRegion(new CellRangeAddress(3, 4, 34, 34)); c111.setCellValue("<1"); c112.setCellValue("1-4Y"); c113.setCellValue("5-9Y"); c114.setCellValue("10-14Y"); c115.setCellValue("15-19Y"); c116.setCellValue("20-24Y"); c117.setCellValue("25-49Y"); c118.setCellValue("50+Y"); c119.setCellValue(""); for (int l = 20; l <= 43; l++) { c113 = rw0.getCell(l); c113.setCellStyle(styleHeader); } double checkdiff = 0; int count = 4; TestedAdultMale = 0; TestedAdultFemale = 0; TestedChildMale = 0; TestedChildFemale = 0; HIV_AdultMale = 0; HIV_AdultFemale = 0; HIV_ChildMale = 0; HIV_ChildFemale = 0; //--------------------------------------------------------------------------- String facilid = ""; String facilname = ""; String dsdta = ""; String get711data = "SELECT(sum(VCTClient_Tested_CF) +sum( VCTClient_Tested_AF)+sum(DTCB_Test_Out_AF)+sum(DTCB_Test_In_AF))" //ADULTS TESTED FEMALE + ",(sum(VCTClient_Tested_CM)+ sum(VCTClient_Tested_AM) + sum(DTCB_Test_Out_AM) + sum(DTCB_Test_In_AM))"//ADULTS TESTED MALES + ", (sum(VCTClient_HIV_CF)+ sum(VCTClient_HIV_AF)+sum(DTCC_HIV_In_AF)+ sum(DTCC_HIV_Out_AF))" // ADULTS HIV+ FEMALE + ",(sum(VCTClient_HIV_CM)+sum(VCTClient_HIV_AM)+ sum(DTCC_HIV_In_AM) +sum(DTCC_HIV_Out_AM)) " // ADULTS HIV+ MALE + ", (sum(DTCB_Test_Out_CF) + sum(DTCB_Test_In_CF))" // CHILDREN TOTAL TESTED FEMALE + ", (sum(DTCB_Test_Out_CM) + sum(DTCB_Test_In_CM))" // CHILDREN TOTAL TESTED MALE + ", ( sum(DTCC_HIV_In_CF)+ sum(DTCC_HIV_Out_CF))" // CHILDREN OSITIVE FEMALE + ", (sum(DTCC_HIV_In_CM)+ sum(DTCC_HIV_Out_CM)),county.County,district.DistrictNom," + "subpartnera.SubPartnerNom,subpartnera.CentreSanteId,subpartnera.HTC_Support1"// CHILDREN POSITIVE MALE + " FROM moh711 JOIN subpartnera " + "ON moh711.SubPartnerID=subpartnera.SubPartnerID " + "JOIN district ON subpartnera.DistrictID=district.DistrictID JOIN county ON " + "district.CountyID=county.CountyID" + " WHERE " + " " + facilityIds1 + " " + duration1 + " && subpartnera.HTC=1 " + "GROUP BY moh711.SubPartnerID "; System.out.println("711 : " + get711data); conn.rs = conn.st.executeQuery(get711data); while (conn.rs.next()) { county = conn.rs.getString(9); district = conn.rs.getString(10); facilityname = conn.rs.getString(11); mflcode = conn.rs.getString(12); dsdta = conn.rs.getString(13); TestedAdultFemale = conn.rs.getInt(1); TestedAdultMale = conn.rs.getInt(2); HIV_AdultFemale = conn.rs.getInt(3); HIV_AdultMale = conn.rs.getInt(4); TestedChildFemale = conn.rs.getInt(5); TestedChildMale = conn.rs.getInt(6); HIV_ChildFemale = conn.rs.getInt(7); HIV_ChildMale = conn.rs.getInt(8); System.out.println(facilityname + " KKK " + HIV_AdultFemale + " " + HIV_AdultMale + " " + HIV_ChildFemale + " " + HIV_ChildMale); System.out.println( facilityname + "TestedChildFemale " + TestedChildFemale + " HIV_ChildFemale " + HIV_ChildFemale + " TestedChildMale " + TestedChildMale + " HIV_ChildMale " + HIV_ChildMale); String basicDetails = county + "@" + district + "@" + facilityname + "@" + mflcode + "@" + dsdta; String arrayDetails[] = basicDetails.split("@"); count++; rw0 = shet3.createRow(count); int facilno = 0; for (int j = 0; j < arrayDetails.length; j++) { HSSFCell S3cell = rw0.createCell(facilno); S3cell.setCellValue(arrayDetails[j]); // System.out.println(arrayDetails[j]); S3cell.setCellStyle(stborder); System.out.println("facildet pos : " + facilno + " det : " + arrayDetails[j]); facilno++; } System.out.println(facilityname + " TestedAdultFemale " + TestedAdultFemale + "TestedAdultMale " + TestedAdultMale + " TestedChildFemale " + TestedChildFemale + " TestedChildMale " + TestedChildMale + " HIV_AdultFemale " + HIV_AdultFemale + " HIV_AdultMale " + HIV_AdultMale + " HIV_ChildFemale " + HIV_ChildFemale + " HIV_ChildMale " + HIV_ChildMale); // FEMALES FemaleAdultTested19 = (float) Math.round((0.05 * TestedAdultFemale)); FemaleAdultTested24 = (float) Math.round((0.11 * TestedAdultFemale)); FemaleAdultTested49 = (float) Math.round((0.72 * TestedAdultFemale)); FemaleAdultTested50 = (float) Math.round((0.12 * TestedAdultFemale)); FemaleTestedChild1 = (float) Math.round((0.05 * TestedChildFemale)); FemaleTestedChild4 = (float) Math.round((0.26 * TestedChildFemale)); FemaleTestedChild9 = (float) Math.round((0.29 * TestedChildFemale)); FemaleTestedChild14 = (float) Math.round((0.40 * TestedChildFemale)); //postive AdultFemaleHIV19 = (float) Math.round((0.02 * HIV_AdultFemale)); AdultFemaleHIV24 = (float) Math.round((0.09 * HIV_AdultFemale)); AdultFemaleHIV49 = (float) Math.round((0.79 * HIV_AdultFemale)); AdultFemaleHIV50 = (float) Math.round((0.10 * HIV_AdultFemale)); //positve ChildFemaleHIV1 = (float) Math.round((0.13 * HIV_ChildFemale)); ChildFemaleHIV4 = (float) Math.round((0.37 * HIV_ChildFemale)); ChildFemaleHIV9 = (float) Math.round((0.25 * HIV_ChildFemale)); ChildFemaleHIV14 = (float) Math.round((0.25 * HIV_ChildFemale)); // MALES MaleAdultTested19 = (float) Math.round((0.05 * TestedAdultMale)); MaleAdultTested24 = (float) Math.round((0.11 * TestedAdultMale)); MaleAdultTested49 = (float) Math.round((0.72 * TestedAdultMale)); MaleAdultTested50 = (float) Math.round((0.12 * TestedAdultMale)); MaleTestedChild1 = (float) Math.round((0.05 * TestedChildMale)); MaleTestedChild4 = (float) Math.round((0.26 * TestedChildMale)); MaleTestedChild9 = (float) Math.round((0.29 * TestedChildMale)); MaleTestedChild14 = (float) Math.round((0.40 * TestedChildMale)); //positive AdultMaleHIV19 = (float) Math.round((0.02 * HIV_AdultMale)); AdultMaleHIV24 = (float) Math.round((0.09 * HIV_AdultMale)); AdultMaleHIV49 = (float) Math.round((0.79 * HIV_AdultMale)); AdultMaleHIV50 = (float) Math.round((0.10 * HIV_AdultMale)); //positives ChildMaleHIV1 = (float) Math.round((0.13 * HIV_ChildMale)); ChildMaleHIV4 = (float) Math.round((0.37 * HIV_ChildMale)); ChildMaleHIV9 = (float) Math.round((0.25 * HIV_ChildMale)); ChildMaleHIV14 = (float) Math.round((0.25 * HIV_ChildMale)); // TestedAdultFemale=conn.rs.getInt(1); // TestedAdultMale=conn.rs.getInt(2); // HIV_AdultFemale=conn.rs.getInt(3); // HIV_AdultMale=conn.rs.getInt(4); // TestedChildFemale=conn.rs.getInt(5); // TestedChildMale=conn.rs.getInt(6); // HIV_ChildFemale=conn.rs.getInt(7); // HIV_ChildMale=conn.rs.getInt(8); // TotalTested=FemaleTestedChild1+FemaleTestedChild4+FemaleTestedChild9+FemaleTestedChild14+FemaleAdultTested19+FemaleAdultTested24+FemaleAdultTested49+FemaleAdultTested50+ MaleAdultTested19+MaleAdultTested24+MaleAdultTested49+MaleAdultTested50+MaleTestedChild1+MaleTestedChild4+MaleTestedChild9+MaleTestedChild14; // TotalNegativeFemale1=AdultFemaleHIV19Neg+AdultFemaleHIV24Neg+AdultFemaleHIV49Neg+AdultFemaleHIV50Neg+ ChildFemaleHIV1Neg+ChildFemaleHIV4Neg+ChildFemaleHIV9Neg+ChildFemaleHIV14Neg; // TotalNegativeMale1=AdultMaleHIV19Neg+AdultMaleHIV24Neg+AdultMaleHIV49Neg+AdultMaleHIV50Neg+ChildMaleHIV1Neg+ChildMaleHIV4Neg+ChildMaleHIV9Neg+ChildMaleHIV14Neg; //negative int neg1male = 0; int neg4male = 0; int neg9male = 0; int neg14male = 0; int neg19male = 0; int neg24male = 0; int neg49male = 0; int neg50male = 0; AdultMaleHIV19Neg = (float) Math.round(MaleAdultTested19) - (AdultMaleHIV19); AdultMaleHIV24Neg = (float) Math.round(MaleAdultTested24) - (AdultMaleHIV24); AdultMaleHIV49Neg = (float) Math.round(MaleAdultTested49) - (AdultMaleHIV49); AdultMaleHIV50Neg = (float) Math.round(MaleAdultTested50) - (AdultMaleHIV50); if (AdultMaleHIV19Neg <= -1) { neg19male = 1; } if (AdultMaleHIV24Neg <= -1) { neg24male = 1; } if (AdultMaleHIV49Neg <= -1) { neg49male = 1; } if (AdultMaleHIV50Neg <= -1) { neg50male = 1; } // child male negatives ChildMaleHIV1Neg = (float) Math.round(MaleTestedChild1) - (ChildMaleHIV1); ChildMaleHIV4Neg = (float) Math.round(MaleTestedChild4) - (ChildMaleHIV4); ChildMaleHIV9Neg = (float) Math.round(MaleTestedChild9) - (ChildMaleHIV9); ChildMaleHIV14Neg = (float) Math.round(MaleTestedChild14) - (ChildMaleHIV14); if (ChildMaleHIV1Neg <= -1) { neg1male = 1; } if (ChildMaleHIV4Neg <= -1) { neg4male = 1; } if (ChildMaleHIV9Neg <= -1) { neg9male = 1; } if (ChildMaleHIV14Neg <= -1) { neg14male = 1; } //negative int neg1female = 0; int neg4female = 0; int neg9female = 0; int neg14female = 0; int neg19female = 0; int neg24female = 0; int neg49female = 0; int neg50female = 0; ChildFemaleHIV1Neg = (float) Math.round(FemaleTestedChild1) - (ChildFemaleHIV1); ChildFemaleHIV4Neg = (float) Math.round(FemaleTestedChild4) - (ChildFemaleHIV4); ChildFemaleHIV9Neg = (float) Math.round(FemaleTestedChild9) - (ChildFemaleHIV9); ChildFemaleHIV14Neg = (float) Math.round(FemaleTestedChild14) - (ChildFemaleHIV14); if (ChildFemaleHIV1Neg <= -1) { neg1female = 1; } if (ChildFemaleHIV4Neg <= -1) { neg4female = 1; } if (ChildFemaleHIV9Neg <= -1) { neg9female = 1; } if (ChildFemaleHIV14Neg <= -1) { neg14female = 1; } System.out.println(facilityname + " fffff " + ChildFemaleHIV1Neg + " " + ChildFemaleHIV4Neg + " " + ChildFemaleHIV9Neg + " " + ChildFemaleHIV14Neg); //negative AdultFemaleHIV19Neg = (float) Math.round(FemaleAdultTested19) - (AdultFemaleHIV19); AdultFemaleHIV24Neg = (float) Math.round(FemaleAdultTested24) - (AdultFemaleHIV24); AdultFemaleHIV49Neg = (float) Math.round(FemaleAdultTested49) - (AdultFemaleHIV49); AdultFemaleHIV50Neg = (float) Math.round(FemaleAdultTested50) - (AdultFemaleHIV50); if (AdultFemaleHIV19Neg <= -1) { neg19female = 1; } if (AdultFemaleHIV24Neg <= -1) { neg24female = 1; } if (AdultFemaleHIV49Neg <= -1) { neg49female = 1; } if (AdultFemaleHIV50Neg <= -1) { neg50female = 1; } double totalpositivesmale = 0; double totalpositivesfemale = 0; double totalpositives = 0; double totalnegatives = 0; double totalfemalehiv = 0; double totalmalehiv = 0; double totalfemaletesteddis = 0; double totalmaletesteddis = 0; double totalfemaletested = 0; double totalmaletested = 0; double negfem = 0; double negmale = 0; int redalert = 0; int redalert1 = 0; int redalert2 = 0; int redalert3 = 0; int finalalert = 0; totalpositives = AdultFemaleHIV19 + AdultFemaleHIV24 + AdultFemaleHIV49 + AdultFemaleHIV50 + ChildFemaleHIV1 + ChildFemaleHIV4 + ChildFemaleHIV9 + ChildFemaleHIV14 + AdultMaleHIV19 + AdultMaleHIV24 + AdultMaleHIV49 + AdultMaleHIV50 + ChildMaleHIV1 + ChildMaleHIV4 + ChildMaleHIV9 + ChildMaleHIV14; totalnegatives = AdultFemaleHIV19Neg + AdultFemaleHIV24Neg + AdultFemaleHIV49Neg + AdultFemaleHIV50Neg + ChildFemaleHIV1Neg + ChildFemaleHIV4Neg + ChildFemaleHIV9Neg + ChildFemaleHIV14Neg + AdultMaleHIV19Neg + AdultMaleHIV24Neg + AdultMaleHIV49Neg + AdultMaleHIV50Neg + ChildMaleHIV1Neg + ChildMaleHIV4Neg + ChildMaleHIV9Neg + ChildMaleHIV14Neg; totalpositivesfemale = AdultFemaleHIV19 + AdultFemaleHIV24 + AdultFemaleHIV49 + AdultFemaleHIV50 + ChildFemaleHIV1 + ChildFemaleHIV4 + ChildFemaleHIV9 + ChildFemaleHIV14; totalpositivesmale = AdultMaleHIV19 + AdultMaleHIV24 + AdultMaleHIV49 + AdultMaleHIV50 + ChildMaleHIV1 + ChildMaleHIV4 + ChildMaleHIV9 + ChildMaleHIV14; // // total tested after distribution totalfemaletesteddis = FemaleTestedChild1 + FemaleTestedChild4 + FemaleTestedChild9 + FemaleTestedChild14 + FemaleAdultTested19 + FemaleAdultTested24 + FemaleAdultTested49 + FemaleAdultTested50; totalmaletesteddis = MaleAdultTested19 + MaleAdultTested24 + MaleAdultTested49 + MaleAdultTested50 + MaleTestedChild1 + MaleTestedChild4 + MaleTestedChild9 + MaleTestedChild14; // totaltested totalfemaletested = TestedAdultFemale + TestedChildFemale; totalmaletested = TestedAdultMale + TestedChildMale; //poistives totalfemalehiv = HIV_AdultFemale + HIV_ChildFemale; totalmalehiv = HIV_AdultMale + HIV_ChildMale; // negative negfem = totalfemaletested - totalfemalehiv; negmale = totalmaletested - totalmalehiv; double TotalNegativeFemale1 = 0; double TotalNegativeMale1 = 0; TotalNegativeFemale1 = AdultFemaleHIV19Neg + AdultFemaleHIV24Neg + AdultFemaleHIV49Neg + AdultFemaleHIV50Neg + ChildFemaleHIV1Neg + ChildFemaleHIV4Neg + ChildFemaleHIV9Neg + ChildFemaleHIV14Neg; TotalNegativeMale1 = AdultMaleHIV19Neg + AdultMaleHIV24Neg + AdultMaleHIV49Neg + AdultMaleHIV50Neg + ChildMaleHIV1Neg + ChildMaleHIV4Neg + ChildMaleHIV9Neg + ChildMaleHIV14Neg; double checkdiff1 = 0; double checkdiff2 = 0; double checkdiff3 = 0; double totalcheckdiff = 0; int redfemalealert = 0; int redmalealert = 0; System.out.println(facilityname + " KKK " + HIV_AdultFemale + " " + HIV_AdultMale + " " + HIV_ChildFemale + " " + HIV_ChildMale); System.out.println( facilityname + "TestedChildFemale " + TestedChildFemale + " HIV_ChildFemale " + HIV_ChildFemale + " TestedChildMale " + TestedChildMale + " HIV_ChildMale " + HIV_ChildMale); double totaltestedmale1 = 0; double totaltestedfemale1 = 0; TotalTested = TestedChildFemale + TestedChildMale + TestedAdultMale + TestedAdultFemale; totaltestedmale1 = TestedChildMale + TestedAdultMale; totaltestedfemale1 = TestedChildFemale + TestedAdultFemale; TotalPositiveFemale = HIV_ChildFemale + HIV_AdultFemale; TotalPositiveMale = HIV_ChildMale + HIV_AdultMale; TotalPositive = HIV_ChildFemale + HIV_AdultFemale + HIV_ChildMale + HIV_AdultMale; TotalNegativeFemale = totaltestedfemale1 - TotalPositiveFemale; TotalNegativeMale = totaltestedmale1 - TotalPositiveMale; checkdiff = totalfemalehiv - totalpositivesfemale; // positive female if (checkdiff > 2 || checkdiff < -2) { redalert = 1; } // positive male checkdiff1 = totalmalehiv - totalpositivesmale; if (checkdiff1 > 2 || checkdiff1 < -2) { redalert1 = 1; } // negative female checkdiff2 = negfem - TotalNegativeFemale1; if (checkdiff2 > 2 || checkdiff2 < -2) { redalert2 = 1; } // negativemale checkdiff3 = negmale - TotalNegativeMale1; if (checkdiff3 > 2 || checkdiff3 < -2) { redalert3 = 1; } totalcheckdiff = TotalTested - (totalfemaletesteddis + totalmaletesteddis); if (totalcheckdiff > 2 || totalcheckdiff < -2) { finalalert = 1; } rw0.setHeightInPoints(25); c211 = rw0.createCell(0); c212 = rw0.createCell(1); c213 = rw0.createCell(2); c214 = rw0.createCell(3); c215 = rw0.createCell(4); c216 = rw0.createCell(5); c217 = rw0.createCell(6); // the rest c11 = rw0.createCell(7); c12 = rw0.createCell(8); c13 = rw0.createCell(9); c14 = rw0.createCell(10); c15 = rw0.createCell(11); c16 = rw0.createCell(12); c17 = rw0.createCell(13); c18 = rw0.createCell(14); c19 = rw0.createCell(15); c20 = rw0.createCell(16); c110 = rw0.createCell(17); c111 = rw0.createCell(18); c112 = rw0.createCell(19); c113 = rw0.createCell(20); c114 = rw0.createCell(21); c115 = rw0.createCell(22); c116 = rw0.createCell(23); c117 = rw0.createCell(24); //c11.setCellValue(facilname); //String basicDetails=county+"@"+district+"@"+facilityname+"@"+mflcode+"@"+dsdta; c211.setCellValue(county); c212.setCellValue(district); c213.setCellValue(facilityname); c214.setCellValue(mflcode); c215.setCellValue(dsdta); shet3.addMergedRegion(new CellRangeAddress(2, 4, 0, 0)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 1, 1)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 2, 2)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 3, 3)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 4, 4)); // Female c11.setCellValue(TotalTested); c216.setCellValue(TotalPositive); c217.setCellValue(TotalPositiveFemale); shet3.addMergedRegion(new CellRangeAddress(2, 4, 5, 5)); shet3.addMergedRegion(new CellRangeAddress(2, 4, 6, 6)); shet3.addMergedRegion(new CellRangeAddress(3, 4, 7, 7)); c12.setCellValue((float) Math.round(ChildFemaleHIV1)); c13.setCellValue((float) Math.round(ChildFemaleHIV4)); c14.setCellValue((float) Math.round(ChildFemaleHIV9)); c15.setCellValue((float) Math.round(ChildFemaleHIV14)); c16.setCellValue((float) Math.round(AdultFemaleHIV19)); c17.setCellValue((float) Math.round(AdultFemaleHIV24)); c18.setCellValue((float) Math.round(AdultFemaleHIV49)); c19.setCellValue((float) Math.round(AdultFemaleHIV50)); c20.setCellValue(TotalPositiveMale); //male c110.setCellValue((float) Math.round(ChildMaleHIV1)); c111.setCellValue((float) Math.round(ChildMaleHIV4)); c112.setCellValue((float) Math.round(ChildMaleHIV9)); c113.setCellValue((float) Math.round(ChildMaleHIV14)); c114.setCellValue((float) Math.round(AdultMaleHIV19)); c115.setCellValue((float) Math.round(AdultMaleHIV24)); c116.setCellValue((float) Math.round(AdultMaleHIV49)); c117.setCellValue((float) Math.round(AdultMaleHIV50)); for (int i = 0; i <= 22; i++) { c11 = rw0.getCell(i); c11.setCellStyle(stborder); if (redalert == 1) { c217 = rw0.getCell(6); c217.setCellStyle(redstyle); } if (redalert1 == 1) { c20 = rw0.getCell(16); c20.setCellStyle(redstyle); } } // shet3.addMergedRegion(new CellRangeAddress(2,5,0,0)); c11 = rw0.createCell(25); c12 = rw0.createCell(26); c13 = rw0.createCell(27); c14 = rw0.createCell(28); c15 = rw0.createCell(29); c16 = rw0.createCell(30); c17 = rw0.createCell(31); c18 = rw0.createCell(32); c19 = rw0.createCell(33); c110 = rw0.createCell(34); c111 = rw0.createCell(35); c112 = rw0.createCell(36); c113 = rw0.createCell(37); c114 = rw0.createCell(38); c115 = rw0.createCell(39); c116 = rw0.createCell(40); c117 = rw0.createCell(41); c118 = rw0.createCell(42); c119 = rw0.createCell(43); c11.setCellValue((float) Math.round(TotalNegativeFemale)); c12.setCellValue((float) Math.round(ChildFemaleHIV1Neg)); c13.setCellValue((float) Math.round(ChildFemaleHIV4Neg)); c14.setCellValue((float) Math.round(ChildFemaleHIV9Neg)); c15.setCellValue((float) Math.round(ChildFemaleHIV14Neg)); c16.setCellValue((float) Math.round(AdultFemaleHIV19Neg)); c17.setCellValue((float) Math.round(AdultFemaleHIV24Neg)); c18.setCellValue((float) Math.round(AdultFemaleHIV49Neg)); c19.setCellValue((float) Math.round(AdultFemaleHIV50Neg)); c110.setCellValue((float) Math.round(TotalNegativeMale)); c111.setCellValue((float) Math.round(ChildMaleHIV1Neg)); c112.setCellValue((float) Math.round(ChildMaleHIV4Neg)); c113.setCellValue((float) Math.round(ChildMaleHIV9Neg)); c114.setCellValue((float) Math.round(ChildMaleHIV14Neg)); c115.setCellValue((float) Math.round(AdultMaleHIV19Neg)); c116.setCellValue((float) Math.round(AdultMaleHIV24Neg)); c117.setCellValue((float) Math.round(AdultMaleHIV49Neg)); c118.setCellValue((float) Math.round(AdultMaleHIV50Neg)); c119.setCellValue(""); System.out.println(facilityname + " jjj " + AdultMaleHIV19Neg + "__________" + AdultMaleHIV24Neg + "__________" + AdultMaleHIV49Neg + "__________" + AdultMaleHIV50Neg + "__________" + ChildMaleHIV1Neg + "__________" + ChildMaleHIV4Neg + "__________" + ChildMaleHIV9Neg + "__________" + ChildMaleHIV14Neg); for (int i = 23; i <= 43; i++) { c11 = rw0.getCell(i); c11.setCellStyle(stborder); if (redalert2 == 1) { c11 = rw0.getCell(25); c11.setCellStyle(redstyle); } if (redalert3 == 1) { c110 = rw0.getCell(34); c110.setCellStyle(redstyle); } if (finalalert == 1) { c119 = rw0.getCell(43); c119.setCellStyle(redstyle); } if (neg1female == 1) { c12 = rw0.getCell(26); c12.setCellStyle(redstyle); } if (neg4female == 1) { c12 = rw0.getCell(27); c12.setCellStyle(redstyle); } if (neg9female == 1) { c13 = rw0.getCell(28); c13.setCellStyle(redstyle); } if (neg14female == 1) { c14 = rw0.getCell(29); c14.setCellStyle(redstyle); } if (neg19female == 1) { c15 = rw0.getCell(30); c15.setCellStyle(redstyle); } if (neg24female == 1) { c16 = rw0.getCell(31); c16.setCellStyle(redstyle); } if (neg49female == 1) { c17 = rw0.getCell(32); c17.setCellStyle(redstyle); } if (neg50female == 1) { c18 = rw0.getCell(33); c18.setCellStyle(redstyle); } //male if (neg1male == 1) { c111 = rw0.getCell(35); c111.setCellStyle(redstyle); } if (neg4male == 1) { c112 = rw0.getCell(36); c112.setCellStyle(redstyle); } if (neg9male == 1) { c113 = rw0.getCell(37); c113.setCellStyle(redstyle); } if (neg14male == 1) { c114 = rw0.getCell(38); c114.setCellStyle(redstyle); } if (neg19male == 1) { c115 = rw0.getCell(39); c115.setCellStyle(redstyle); } if (neg24male == 1) { c116 = rw0.getCell(40); c116.setCellStyle(redstyle); } if (neg49male == 1) { c117 = rw0.getCell(41); c117.setCellStyle(redstyle); } if (neg50male == 1) { c118 = rw0.getCell(42); c118.setCellStyle(redstyle); } } // shet3.addMergedRegion(new CellRangeAddress(2,5,20,20)); } if (conn.st != null) { conn.st.close(); } if (conn.st1 != null) { conn.st1.close(); } if (conn.st2 != null) { conn.st2.close(); } if (conn.rs != null) { conn.rs.close(); } if (conn.rs1 != null) { conn.rs1.close(); } if (conn.rs2 != null) { conn.rs2.close(); } if (conn.conn != null) { conn.conn.close(); } IdGenerator IG = new IdGenerator(); createdOn = IG.CreatedOn(); // write it as an excel attachment ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); response.setContentType("application/ms-excel"); response.setContentLength(outArray.length); response.setHeader("Expires:", "0"); // eliminates browser caching response.setHeader("Content-Disposition", "attachment; filename=DATIM_VERIFICATION_REPORT_CREATED_ON_" + createdOn.trim() + ".xls"); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }