public String execute() throws Exception { statementManager.initialise(); ExportReport exportReport = exportReportService.getExportReport(exportReportId); for (String itemId : itemIds) { ImportItem importItem = importReportService.getImportItem(Integer.parseInt(itemId)); ExportItem newExportItem = new ExportItem(); newExportItem.setName(importItem.getName()); newExportItem.setItemType(itemType); newExportItem.setPeriodType(periodType); newExportItem.setExpression(importItem.getExpression()); newExportItem.setRow(importItem.getRow()); newExportItem.setColumn(importItem.getColumn()); newExportItem.setSheetNo(sheetNo); newExportItem.setExportReport(exportReport); exportReportService.addExportItem(newExportItem); } message = i18n.getString("success"); statementManager.destroy(); return SUCCESS; }
private void updateRelationshipAttributes() { StatementHolder holder = statementManager.getHolder(); try { Statement statement = holder.getStatement(); ResultSet resultSet = statement.executeQuery( "SELECT distinct programid, patientattributeid FROM program_patientAttributes"); while (resultSet.next()) { executeSql( "INSERT into program_patientAttributes( programid, patientattributeid) values (" + resultSet.getString(1) + "," + resultSet.getString(2) + ")"); } executeSql("ALTER TABLE patientattribute DROP COLUMN programid"); } catch (Exception ex) { log.debug(ex); } finally { holder.close(); } }
private void updateProgramStageTabularReportTable() { try { StatementHolder holder = statementManager.getHolder(); Statement statement = holder.getStatement(); ResultSet resultSet = statement.executeQuery( "SELECT pd.patienttabularreportid, tr.programstageid, pd.elt, sort_order " + " FROM patienttabularreport_dataelements pd inner join patienttabularreport tr" + " on pd.patienttabularreportid=tr.patienttabularreportid" + " order by pd.patienttabularreportid"); while (resultSet.next()) { executeSql( "INSERT INTO patienttabularreport_programstagedataelements ( patienttabularreportid, programstageid, dataelementid, sort_order ) VALUES ( " + resultSet.getInt(1) + ", " + resultSet.getInt(2) + ", " + resultSet.getInt(3) + ", " + resultSet.getInt(4) + ")"); } executeSql("ALTER TABLE patienttabularreport DROP COLUMN programstageid"); executeSql( "UPDATE program SET displayIncidentDate=true WHERE displayIncidentDate is null and type!=3"); executeSql( "UPDATE program SET displayIncidentDate=false WHERE displayIncidentDate is null and type==3"); } catch (Exception e) { } }
private void updateProgramStageInstanceOrgunit() { StatementHolder holder = statementManager.getHolder(); try { Statement statement = holder.getStatement(); ResultSet resultSet = statement.executeQuery( "SELECT distinct programstageinstanceid, organisationunitid, providedByAnotherFacility FROM patientdatavalue"); while (resultSet.next()) { executeSql( "UPDATE programstageinstance SET organisationunitid=" + resultSet.getInt(2) + ", providedByAnotherFacility=" + resultSet.getBoolean(3) + " WHERE programstageinstanceid=" + resultSet.getInt(1)); } executeSql("ALTER TABLE patientdatavalue DROP COLUMN organisationUnitid"); executeSql("ALTER TABLE patientdatavalue DROP COLUMN providedByAnotherFacility"); executeSql( "ALTER TABLE patientdatavalue ADD PRIMARY KEY ( programstageinstanceid, dataelementid )"); } catch (Exception ex) { log.debug(ex); } finally { holder.close(); } }
private int executeSql(String sql) { try { return statementManager.getHolder().executeUpdate(sql); } catch (Exception ex) { log.debug("Execution error", ex); return -1; } }
// ------------------------------------------------------------------------- // Action implementation // ------------------------------------------------------------------------- public String execute() throws Exception { statementManager.initialise(); Program selProgram = programService.getProgram(programList); OrganisationUnit selOrgUnit = organisationUnitService.getOrganisationUnit(ouIDTB); System.out.println( "NBITS Report_" + selOrgUnit.getName() + "_" + selProgram.getName() + "_StartTime: " + new Date()); List<OrganisationUnit> orgUnitList = new ArrayList<OrganisationUnit>( organisationUnitService.getOrganisationUnitWithChildren(ouIDTB)); List<OrganisationUnit> programOrgUnits = new ArrayList<OrganisationUnit>(selProgram.getOrganisationUnits()); orgUnitList.retainAll(programOrgUnits); Date sDate = format.parseDate(startDate); Date eDate = format.parseDate(endDate); System.out.println("Start Date" + sDate + "-----" + "End Date: " + eDate); generateReport(selProgram, orgUnitList, sDate, eDate); System.out.println( "NBITS Report_" + selOrgUnit.getName() + "_" + selProgram.getName() + "_EndTime: " + new Date()); statementManager.destroy(); return SUCCESS; }
private void moveStoredByFormStageInstanceToDataValue() { try { StatementHolder holder = statementManager.getHolder(); Statement statement = holder.getStatement(); ResultSet resultSet = statement.executeQuery( "SELECT programstageinstanceid, storedBy" + " FROM programstageinstance where storedBy is not null"); while (resultSet.next()) { executeSql( "UPDATE patientdatavalue SET storedBy='" + resultSet.getString(2) + "' where programstageinstanceid=" + resultSet.getInt(1)); } executeSql("ALTER TABLE programstageinstance DROP COLUMN storedBy"); } catch (Exception ex) { } }
private void updateMultiOrgunitTabularReportTable() { try { StatementHolder holder = statementManager.getHolder(); Statement statement = holder.getStatement(); ResultSet resultSet = statement.executeQuery( "SELECT patienttabularreportid, organisationunitid FROM patienttabularreport"); while (resultSet.next()) { executeSql( " INSERT INTO patienttabularreport_organisationUnits ( patienttabularreportid, organisationunitid ) VALUES ( " + resultSet.getInt(1) + ", " + resultSet.getInt(2) + ")"); } executeSql("ALTER TABLE patienttabularreport DROP COLUMN organisationunitid"); } catch (Exception e) { } }
// ------------------------------------------------------------------------- // Action Implementation // ------------------------------------------------------------------------- public String execute() throws Exception { statementManager.initialise(); // Initialization raFolderName = reportService.getRAFolderName(); String deCodesXMLFileName = ""; simpleDateFormat = new SimpleDateFormat("MMM-yyyy"); monthFormat = new SimpleDateFormat("MMMM"); yearFormat = new SimpleDateFormat("yyyy"); simpleMonthFormat = new SimpleDateFormat("MMM"); String parentUnit = ""; Report_in selReportObj = reportService.getReport(Integer.parseInt(reportList)); deCodesXMLFileName = selReportObj.getXmlTemplateName(); reportModelTB = selReportObj.getModel(); reportFileNameTB = selReportObj.getExcelTemplateName(); String inputTemplatePath = System.getenv("DHIS2_HOME") + File.separator + raFolderName + File.separator + "template" + File.separator + reportFileNameTB; // String outputReportFolderPath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName // + File.separator + "output" + File.separator + UUID.randomUUID().toString(); String outputReportFolderPath = System.getenv("DHIS2_HOME") + File.separator + Configuration_IN.DEFAULT_TEMPFOLDER + File.separator + UUID.randomUUID().toString(); File newdir = new File(outputReportFolderPath); if (!newdir.exists()) { newdir.mkdirs(); } if (reportModelTB.equalsIgnoreCase("STATIC") || reportModelTB.equalsIgnoreCase("STATIC-DATAELEMENTS") || reportModelTB.equalsIgnoreCase("STATIC-FINANCIAL")) { orgUnitList = new ArrayList<OrganisationUnit>( organisationUnitService.getOrganisationUnitWithChildren(ouIDTB)); OrganisationUnitGroup orgUnitGroup = selReportObj.getOrgunitGroup(); orgUnitList.retainAll(orgUnitGroup.getMembers()); } else { return INPUT; } // System.out.println( "---Size of Org Unit List ----: " + orgUnitList.size() + ",Report Group // name is :---" + selReportObj.getOrgunitGroup().getName() + ", Size of Group member is ----:" // + selReportObj.getOrgunitGroup().getMembers().size() ); System.out.println(" ---- Size of OrgUnit List is ---- " + orgUnitList.size()); OrganisationUnit selOrgUnit = organisationUnitService.getOrganisationUnit(ouIDTB); System.out.println( selOrgUnit.getName() + " : " + selReportObj.getName() + " : Report Generation Start Time is : " + new Date()); selectedPeriod = periodService.getPeriod(availablePeriods); sDate = format.parseDate(String.valueOf(selectedPeriod.getStartDate())); eDate = format.parseDate(String.valueOf(selectedPeriod.getEndDate())); Workbook templateWorkbook = Workbook.getWorkbook(new File(inputTemplatePath)); // collect periodId by commaSepareted List<Period> tempPeriodList = new ArrayList<Period>(periodService.getIntersectingPeriods(sDate, eDate)); Collection<Integer> tempPeriodIds = new ArrayList<Integer>(getIdentifiers(Period.class, tempPeriodList)); String periodIdsByComma = getCommaDelimitedString(tempPeriodIds); // Getting DataValues List<Report_inDesign> reportDesignList = reportService.getReportDesign(deCodesXMLFileName); // collect dataElementIDs by commaSepareted String dataElmentIdsByComma = reportService.getDataelementIds(reportDesignList); int orgUnitCount = 0; Iterator<OrganisationUnit> it = orgUnitList.iterator(); while (it.hasNext()) { OrganisationUnit currentOrgUnit = (OrganisationUnit) it.next(); String outPutFileName = reportFileNameTB.replace(".xls", ""); outPutFileName += "_" + currentOrgUnit.getShortName(); outPutFileName += "_" + simpleDateFormat.format(selectedPeriod.getStartDate()) + ".xls"; String outputReportPath = outputReportFolderPath + File.separator + outPutFileName; WritableWorkbook outputReportWorkbook = Workbook.createWorkbook(new File(outputReportPath), templateWorkbook); Map<String, String> aggDeMap = new HashMap<String, String>(); if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) { aggDeMap.putAll( reportService.getResultDataValueFromAggregateTable( currentOrgUnit.getId(), dataElmentIdsByComma, periodIdsByComma)); } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) { List<OrganisationUnit> childOrgUnitTree = new ArrayList<OrganisationUnit>( organisationUnitService.getOrganisationUnitWithChildren(currentOrgUnit.getId())); List<Integer> childOrgUnitTreeIds = new ArrayList<Integer>(getIdentifiers(OrganisationUnit.class, childOrgUnitTree)); String childOrgUnitsByComma = getCommaDelimitedString(childOrgUnitTreeIds); aggDeMap.putAll( reportService.getAggDataFromDataValueTable( childOrgUnitsByComma, dataElmentIdsByComma, periodIdsByComma)); } else if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) { aggDeMap.putAll( reportService.getAggDataFromDataValueTable( "" + currentOrgUnit.getId(), dataElmentIdsByComma, periodIdsByComma)); } int count1 = 0; Iterator<Report_inDesign> reportDesignIterator = reportDesignList.iterator(); while (reportDesignIterator.hasNext()) { Report_inDesign report_inDesign = (Report_inDesign) reportDesignIterator.next(); String deType = report_inDesign.getPtype(); String sType = report_inDesign.getStype(); String deCodeString = report_inDesign.getExpression(); String tempStr = ""; Calendar tempStartDate = Calendar.getInstance(); Calendar tempEndDate = Calendar.getInstance(); List<Calendar> calendarList = new ArrayList<Calendar>(reportService.getStartingEndingPeriods(deType, selectedPeriod)); if (calendarList == null || calendarList.isEmpty()) { tempStartDate.setTime(selectedPeriod.getStartDate()); tempEndDate.setTime(selectedPeriod.getEndDate()); return SUCCESS; } else { tempStartDate = calendarList.get(0); tempEndDate = calendarList.get(1); } if (deCodeString.equalsIgnoreCase("FACILITY")) { tempStr = currentOrgUnit.getName(); } else if (deCodeString.equalsIgnoreCase("FACILITY-NOREPEAT")) { tempStr = parentUnit; } else if (deCodeString.equalsIgnoreCase("FACILITYP")) { tempStr = currentOrgUnit.getParent().getName(); } else if (deCodeString.equalsIgnoreCase("FACILITYPP")) { tempStr = currentOrgUnit.getParent().getParent().getName(); } else if (deCodeString.equalsIgnoreCase("FACILITYPPP")) { tempStr = currentOrgUnit.getParent().getParent().getParent().getName(); } else if (deCodeString.equalsIgnoreCase("FACILITYPPPP")) { tempStr = currentOrgUnit.getParent().getParent().getParent().getParent().getName(); } else if (deCodeString.equalsIgnoreCase("PERIOD") || deCodeString.equalsIgnoreCase("PERIOD-NOREPEAT")) { tempStr = simpleDateFormat.format(sDate); } else if (deCodeString.equalsIgnoreCase("PERIOD-MONTH")) { tempStr = monthFormat.format(sDate); } else if (deCodeString.equalsIgnoreCase("PERIOD-YEAR")) { tempStr = yearFormat.format(sDate); } else if (deCodeString.equalsIgnoreCase("MONTH-START-SHORT")) { tempStr = simpleMonthFormat.format(sDate); } else if (deCodeString.equalsIgnoreCase("MONTH-END-SHORT")) { tempStr = simpleMonthFormat.format(eDate); } else if (deCodeString.equalsIgnoreCase("MONTH-START")) { tempStr = monthFormat.format(sDate); } else if (deCodeString.equalsIgnoreCase("MONTH-END")) { tempStr = monthFormat.format(eDate); } else if (deCodeString.equalsIgnoreCase("SLNO")) { tempStr = "" + (orgUnitCount + 1); } else if (deCodeString.equalsIgnoreCase("NA")) { tempStr = " "; } else { if (sType.equalsIgnoreCase("dataelement")) { if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) { tempStr = getAggVal(deCodeString, aggDeMap); // tempStr = reportService.getIndividualResultDataValue(deCodeString, // tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB ); } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) { tempStr = getAggVal(deCodeString, aggDeMap); // tempStr = reportService.getResultDataValue( deCodeString, tempStartDate.getTime(), // tempEndDate.getTime(), currentOrgUnit, reportModelTB ); } else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) { tempStr = getAggVal(deCodeString, aggDeMap); /* List<Period> periodList = new ArrayList<Period>( periodService.getPeriodsBetweenDates( tempStartDate.getTime(), tempEndDate.getTime() ) ); Collection<Integer> periodIds = new ArrayList<Integer>( getIdentifiers(Period.class, periodList ) ); tempStr = reportService.getResultDataValueFromAggregateTable( deCodeString, periodIds, currentOrgUnit, reportModelTB ); */ } } else if (sType.equalsIgnoreCase("dataelement-boolean")) { if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) { tempStr = reportService.getBooleanDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB); } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) { tempStr = reportService.getBooleanDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB); } else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) { tempStr = reportService.getBooleanDataValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit, reportModelTB); } } else { if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) { tempStr = reportService.getIndividualResultIndicatorValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit); } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) { tempStr = reportService.getResultIndicatorValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit); } else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) { // List<Period> periodList = new ArrayList<Period>( // periodService.getPeriodsBetweenDates( tempStartDate.getTime(), // tempEndDate.getTime() ) ); // Collection<Integer> periodIds = new ArrayList<Integer>( // getIdentifiers(Period.class, periodList ) ); tempStr = reportService.getResultIndicatorValue( deCodeString, tempStartDate.getTime(), tempEndDate.getTime(), currentOrgUnit); } } } int tempRowNo = report_inDesign.getRowno(); int tempColNo = report_inDesign.getColno(); int sheetNo = report_inDesign.getSheetno(); WritableSheet sheet0 = outputReportWorkbook.getSheet(sheetNo); if (tempStr == null || tempStr.equals(" ")) { tempColNo += orgUnitCount; WritableCellFormat wCellformat = new WritableCellFormat(); wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN); wCellformat.setWrap(true); wCellformat.setAlignment(Alignment.CENTRE); sheet0.addCell(new Blank(tempColNo, tempRowNo, wCellformat)); } else { if (reportModelTB.equalsIgnoreCase("DYNAMIC-ORGUNIT")) { if (deCodeString.equalsIgnoreCase("FACILITYP") || deCodeString.equalsIgnoreCase("FACILITYPP") || deCodeString.equalsIgnoreCase("FACILITYPPP") || deCodeString.equalsIgnoreCase("FACILITYPPPP")) { } else if (deCodeString.equalsIgnoreCase("PERIOD") || deCodeString.equalsIgnoreCase("PERIOD-NOREPEAT") || deCodeString.equalsIgnoreCase("PERIOD-WEEK") || deCodeString.equalsIgnoreCase("PERIOD-MONTH") || deCodeString.equalsIgnoreCase("PERIOD-QUARTER") || deCodeString.equalsIgnoreCase("PERIOD-YEAR") || deCodeString.equalsIgnoreCase("MONTH-START") || deCodeString.equalsIgnoreCase("MONTH-END") || deCodeString.equalsIgnoreCase("MONTH-START-SHORT") || deCodeString.equalsIgnoreCase("MONTH-END-SHORT") || deCodeString.equalsIgnoreCase("SIMPLE-QUARTER") || deCodeString.equalsIgnoreCase("QUARTER-MONTHS-SHORT") || deCodeString.equalsIgnoreCase("QUARTER-MONTHS") || deCodeString.equalsIgnoreCase("QUARTER-START-SHORT") || deCodeString.equalsIgnoreCase("QUARTER-END-SHORT") || deCodeString.equalsIgnoreCase("QUARTER-START") || deCodeString.equalsIgnoreCase("QUARTER-END") || deCodeString.equalsIgnoreCase("SIMPLE-YEAR") || deCodeString.equalsIgnoreCase("YEAR-END") || deCodeString.equalsIgnoreCase("YEAR-FROMTO")) { } else { tempColNo += orgUnitCount; } } else if (reportModelTB.equalsIgnoreCase("dynamicwithrootfacility")) { if (deCodeString.equalsIgnoreCase("FACILITYP") || deCodeString.equalsIgnoreCase("FACILITY-NOREPEAT") || deCodeString.equalsIgnoreCase("FACILITYPP") || deCodeString.equalsIgnoreCase("FACILITYPPP") || deCodeString.equalsIgnoreCase("FACILITYPPPP")) { } else if (deCodeString.equalsIgnoreCase("PERIOD") || deCodeString.equalsIgnoreCase("PERIOD-NOREPEAT") || deCodeString.equalsIgnoreCase("PERIOD-WEEK") || deCodeString.equalsIgnoreCase("PERIOD-MONTH") || deCodeString.equalsIgnoreCase("PERIOD-QUARTER") || deCodeString.equalsIgnoreCase("PERIOD-YEAR") || deCodeString.equalsIgnoreCase("MONTH-START") || deCodeString.equalsIgnoreCase("MONTH-END") || deCodeString.equalsIgnoreCase("MONTH-START-SHORT") || deCodeString.equalsIgnoreCase("MONTH-END-SHORT") || deCodeString.equalsIgnoreCase("SIMPLE-QUARTER") || deCodeString.equalsIgnoreCase("QUARTER-MONTHS-SHORT") || deCodeString.equalsIgnoreCase("QUARTER-MONTHS") || deCodeString.equalsIgnoreCase("QUARTER-START-SHORT") || deCodeString.equalsIgnoreCase("QUARTER-END-SHORT") || deCodeString.equalsIgnoreCase("QUARTER-START") || deCodeString.equalsIgnoreCase("QUARTER-END") || deCodeString.equalsIgnoreCase("SIMPLE-YEAR") || deCodeString.equalsIgnoreCase("YEAR-END") || deCodeString.equalsIgnoreCase("YEAR-FROMTO")) { } else { tempRowNo += orgUnitCount; } } WritableCell cell = sheet0.getWritableCell(tempColNo, tempRowNo); CellFormat cellFormat = cell.getCellFormat(); WritableCellFormat wCellformat = new WritableCellFormat(); wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN); wCellformat.setWrap(true); wCellformat.setAlignment(Alignment.CENTRE); if (cell.getType() == CellType.LABEL) { Label l = (Label) cell; l.setString(tempStr); l.setCellFormat(cellFormat); } else { try { sheet0.addCell( new Number(tempColNo, tempRowNo, Double.parseDouble(tempStr), wCellformat)); } catch (Exception e) { sheet0.addCell(new Label(tempColNo, tempRowNo, tempStr, wCellformat)); } } } count1++; } // inner while loop end outputReportWorkbook.write(); outputReportWorkbook.close(); orgUnitCount++; } // outer while loop end statementManager.destroy(); if (zipDirectory(outputReportFolderPath, outputReportFolderPath + ".zip")) { System.out.println( selOrgUnit.getName() + " : " + selReportObj.getName() + " Report Generation End Time is : " + new Date()); fileName = reportFileNameTB.replace(".xls", ""); fileName += "_" + selOrgUnit.getShortName(); fileName += "_" + simpleDateFormat.format(selectedPeriod.getStartDate()) + ".zip"; File outputReportFile = new File(outputReportFolderPath + ".zip"); inputStream = new BufferedInputStream(new FileInputStream(outputReportFile)); return SUCCESS; } else { return INPUT; } }
@Override public void execute() { executeSql("update mapview set valuetype=mapvaluetype where valuetype is null"); executeSql("update mapview set legendtype=maplegendtype where legendtype is null"); executeSql("update mapview set legendsetid=maplegendsetid where legendsetid is null"); executeSql("alter table mapview drop column mapvaluetype"); executeSql("alter table mapview drop column maplegendtype"); executeSql("alter table mapview drop column maplegendsetid"); executeSql("alter table mapview drop column bounds"); executeSql("alter table mapview drop column code"); executeSql("alter table mapview drop column periodtypeid"); executeSql("update mapview set layer='thematic1' where layer is null"); executeSql("update mapview set valuetype='dataElement' where valuetype='dataelement'"); executeSql("alter table mapview alter column opacity type double precision"); String sql = "select mapviewid, name, userid, longitude, latitude, zoom from mapview where mapviewid not in (" + "select mapviewid from mapmapviews)"; BatchHandler<Map> batchHandler = batchHandlerFactory.createBatchHandler(MapBatchHandler.class).init(); try { ResultSet rs = statementManager.getHolder().getStatement().executeQuery(sql); while (rs.next()) { log.info("Upgrading map view..."); User user = null; int userId = rs.getInt("userid"); int mapViewId = rs.getInt("mapviewid"); if (userId != 0) { user = new User(); user.setId(userId); } String uid = CodeGenerator.generateCode(); Map map = new Map( rs.getString("name"), user, rs.getDouble("longitude"), rs.getDouble("latitude"), rs.getInt("zoom")); map.setUid(uid); int mapId = batchHandler.insertObject(map, true); statementManager .getHolder() .executeUpdate( "insert into mapmapviews (mapid,mapviewid,sort_order) values(" + mapId + "," + mapViewId + ",0);"); log.info("Upgraded map view: " + map); } } catch (Exception ex) { log.debug("Error", ex); return; } finally { batchHandler.flush(); } executeSql("alter table mapview drop column name"); executeSql("alter table mapview drop column userid"); executeSql("alter table mapview drop column longitude"); executeSql("alter table mapview drop column latitude"); executeSql("alter table mapview drop column zoom"); }
// ------------------------------------------------------------------------- // Action implementation // ------------------------------------------------------------------------- public String execute() throws Exception { statementManager.initialise(); // Initialization raFolderName = reportService.getRAFolderName(); String colArray[] = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ" }; Report_in selReportObj = reportService.getReport(Integer.parseInt(reportList)); // OrgUnit Info OrganisationUnit currentOrgUnit = organisationUnitService.getOrganisationUnit(ouIDTB); System.out.println( currentOrgUnit.getName() + " : " + selReportObj.getName() + " : Report Generation Start Time is : " + new Date()); List<OrganisationUnit> childOrgUnitTree = new ArrayList<OrganisationUnit>( organisationUnitService.getOrganisationUnitWithChildren(ouIDTB)); List<Integer> childOrgUnitTreeIds = new ArrayList<Integer>(getIdentifiers(OrganisationUnit.class, childOrgUnitTree)); String childOrgUnitsByComma = getCommaDelimitedString(childOrgUnitTreeIds); // Report Info String deCodesXMLFileName = selReportObj.getXmlTemplateName(); String reportModelTB = selReportObj.getModel(); String reportFileNameTB = selReportObj.getExcelTemplateName(); String inputTemplatePath = System.getenv("DHIS2_HOME") + File.separator + raFolderName + File.separator + "template" + File.separator + reportFileNameTB; // String outputReportPath = System.getenv( "DHIS2_HOME" ) + File.separator + raFolderName + // File.separator + "output" + File.separator + UUID.randomUUID().toString() + ".xls"; String outputReportPath = System.getenv("DHIS2_HOME") + File.separator + Configuration_IN.DEFAULT_TEMPFOLDER; File newdir = new File(outputReportPath); if (!newdir.exists()) { newdir.mkdirs(); } outputReportPath += File.separator + UUID.randomUUID().toString() + ".xls"; Workbook templateWorkbook = Workbook.getWorkbook(new File(inputTemplatePath)); WritableWorkbook outputReportWorkbook = Workbook.createWorkbook(new File(outputReportPath), templateWorkbook); WritableCellFormat wCellformat = new WritableCellFormat(); wCellformat.setBorder(Border.ALL, BorderLineStyle.THIN); wCellformat.setAlignment(Alignment.CENTRE); wCellformat.setVerticalAlignment(VerticalAlignment.CENTRE); wCellformat.setWrap(true); // Period Info selectedPeriod = periodService.getPeriod(availablePeriods); selectedEndPeriod = periodService.getPeriod(availablePeriodsto); sDate = format.parseDate(String.valueOf(selectedPeriod.getStartDate())); eDate = format.parseDate(String.valueOf(selectedEndPeriod.getEndDate())); PeriodType periodType = periodService.getPeriodTypeByName(periodTypeId); List<Period> periodList = new ArrayList<Period>(periodService.getPeriodsBetweenDates(periodType, sDate, eDate)); // List<Period> periodList = new ArrayList<Period>( periodService.getIntersectingPeriods( sDate, // eDate ) ); Collections.sort(periodList, new PeriodStartDateComparator()); if (periodTypeId.equalsIgnoreCase("monthly")) { simpleDateFormat = new SimpleDateFormat("MMM-yyyy"); } else if (periodTypeId.equalsIgnoreCase("yearly")) { simpleDateFormat = new SimpleDateFormat("yyyy"); } else { simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); } // To get Aggregation Data List<Report_inDesign> reportDesignList = reportService.getReportDesign(deCodesXMLFileName); // String dataElmentIdsByComma = reportService.getDataelementIds( reportDesignList ); String dataElmentIdsByComma = reportService.getDataelementIdsByStype(reportDesignList, Report_inDesign.ST_DATAELEMENT); String nonNumberDataElementIdsByComma = reportService.getDataelementIdsByStype( reportDesignList, Report_inDesign.ST_NON_NUMBER_DATAELEMENT); // Collection<Integer> periodIds1 = new ArrayList<Integer>( getIdentifiers(Period.class, // periodList ) ); String periodsByComma = ""; // getCommaDelimitedString( periodIds1 ); int colCount = 0; for (Period period : periodList) { if (periodTypeId.equalsIgnoreCase("daily")) { periodsByComma = "" + period.getId(); } else { Collection<Integer> periodIds = new ArrayList<Integer>( getIdentifiers( Period.class, periodService.getIntersectingPeriods( period.getStartDate(), period.getEndDate()))); periodsByComma = getCommaDelimitedString(periodIds); } Map<String, String> aggDeMap = new HashMap<String, String>(); if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) { aggDeMap.putAll( reportService.getResultDataValueFromAggregateTable( currentOrgUnit.getId(), dataElmentIdsByComma, periodsByComma)); } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) { aggDeMap.putAll( reportService.getAggDataFromDataValueTable( childOrgUnitsByComma, dataElmentIdsByComma, periodsByComma)); aggDeMap.putAll( reportService.getAggNonNumberDataFromDataValueTable( childOrgUnitsByComma, nonNumberDataElementIdsByComma, periodsByComma)); System.out.println( childOrgUnitsByComma + " \n " + dataElmentIdsByComma + " \n " + periodsByComma); } else if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) { aggDeMap.putAll( reportService.getAggDataFromDataValueTable( "" + currentOrgUnit.getId(), dataElmentIdsByComma, periodsByComma)); aggDeMap.putAll( reportService.getAggNonNumberDataFromDataValueTable( "" + currentOrgUnit.getId(), nonNumberDataElementIdsByComma, periodsByComma)); } System.out.println("aggDeMap size : " + aggDeMap.size()); Iterator<Report_inDesign> reportDesignIterator = reportDesignList.iterator(); while (reportDesignIterator.hasNext()) { Report_inDesign reportDesign = reportDesignIterator.next(); String deCodeString = reportDesign.getExpression(); String sType = reportDesign.getStype(); String tempStr = ""; tempRowNo = reportDesign.getRowno(); tempColNo = reportDesign.getColno(); sheetNo = reportDesign.getSheetno(); if (deCodeString.equalsIgnoreCase("FACILITY")) { tempStr = currentOrgUnit.getName(); } else if (deCodeString.equalsIgnoreCase("PERIOD-RANGE")) { tempStr = simpleDateFormat.format(selectedPeriod.getStartDate()) + " To " + simpleDateFormat.format(selectedEndPeriod.getEndDate()); } else if (deCodeString.equalsIgnoreCase("PROGRESSIVE-PERIOD")) { tempStr = simpleDateFormat.format(period.getStartDate()); } else if (deCodeString.equalsIgnoreCase("NA")) { tempStr = " "; } else { if (sType.equalsIgnoreCase("dataelement")) { if (aggData.equalsIgnoreCase(USECAPTUREDDATA)) { // tempStr = reportService.getIndividualResultDataValue( deCodeString, // period.getStartDate(), period.getEndDate(), currentOrgUnit, reportModelTB ); tempStr = getAggVal(deCodeString, aggDeMap); } else if (aggData.equalsIgnoreCase(GENERATEAGGDATA)) { // tempStr = reportService.getResultDataValue( deCodeString, period.getStartDate(), // period.getEndDate(), currentOrgUnit, reportModelTB ); tempStr = getAggVal(deCodeString, aggDeMap); } else if (aggData.equalsIgnoreCase(USEEXISTINGAGGDATA)) { tempStr = getAggVal(deCodeString, aggDeMap); } } else if (sType.equalsIgnoreCase(Report_inDesign.ST_DATAELEMENT_NO_REPEAT)) { deCodeString = deCodeString.replaceAll(":", "\\."); deCodeString = deCodeString.replaceAll("[", ""); deCodeString = deCodeString.replaceAll("]", ""); System.out.println("deCodeString : " + deCodeString); tempStr = aggDeMap.get(deCodeString); } } if (tempStr == null || tempStr.equals(" ")) { tempColNo += colCount; WritableSheet sheet0 = outputReportWorkbook.getSheet(sheetNo); sheet0.addCell(new Blank(tempColNo, tempRowNo, wCellformat)); } else { if (reportModelTB.equalsIgnoreCase("PROGRESSIVE-PERIOD")) { if (deCodeString.equalsIgnoreCase("FACILITY") || deCodeString.equalsIgnoreCase("PERIOD-RANGE")) { } else { tempColNo += colCount; } WritableSheet sheet0 = outputReportWorkbook.getSheet(sheetNo); try { try { sheet0.addCell( new Number(tempColNo, tempRowNo, Double.parseDouble(tempStr), wCellformat)); } catch (Exception e) { sheet0.addCell(new Label(tempColNo, tempRowNo, tempStr, wCellformat)); } } catch (Exception e) { System.out.println("Cannot write to Excel"); } } } } // inner while loop end colCount++; } // outer while loop end // --------------------------------------------------------------------- // Writing Total Values // --------------------------------------------------------------------- Iterator<Report_inDesign> reportDesignIterator = reportDesignList.iterator(); while (reportDesignIterator.hasNext()) { Report_inDesign reportDesign = reportDesignIterator.next(); String deCodeString = reportDesign.getExpression(); if (deCodeString.equalsIgnoreCase("FACILITY") || deCodeString.equalsIgnoreCase("PERIOD-RANGE")) { continue; } tempRowNo = reportDesign.getRowno(); tempColNo = reportDesign.getColno(); sheetNo = reportDesign.getSheetno(); String colStart = "" + colArray[tempColNo]; String colEnd = "" + colArray[tempColNo + colCount - 1]; String tempFormula = "SUM(" + colStart + (tempRowNo + 1) + ":" + colEnd + (tempRowNo + 1) + ")"; WritableSheet totalSheet = outputReportWorkbook.getSheet(sheetNo); WritableFont arialBold = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD); WritableCellFormat totalCellformat = new WritableCellFormat(arialBold); totalCellformat.setBorder(Border.ALL, BorderLineStyle.THIN); totalCellformat.setAlignment(Alignment.CENTRE); totalCellformat.setVerticalAlignment(VerticalAlignment.CENTRE); totalCellformat.setWrap(true); if (deCodeString.equalsIgnoreCase("PROGRESSIVE-PERIOD")) { totalSheet.addCell(new Label(tempColNo + colCount, tempRowNo, "Total", totalCellformat)); } else if (deCodeString.equalsIgnoreCase("NA")) { totalSheet.addCell(new Label(tempColNo + colCount, tempRowNo, " ", totalCellformat)); } else { totalSheet.addCell( new Formula(tempColNo + colCount, tempRowNo, tempFormula, totalCellformat)); } } outputReportWorkbook.write(); outputReportWorkbook.close(); fileName = reportFileNameTB.replace(".xls", ""); fileName += "_" + currentOrgUnit.getShortName(); fileName += "_" + simpleDateFormat.format(selectedPeriod.getStartDate()) + ".xls"; File outputReportFile = new File(outputReportPath); inputStream = new BufferedInputStream(new FileInputStream(outputReportFile)); System.out.println( currentOrgUnit.getName() + " : " + selReportObj.getName() + " : Report Generation End Time is : " + new Date()); outputReportFile.deleteOnExit(); statementManager.destroy(); return SUCCESS; }
private void updateCaseAggregationCondition() { String regExp = "\\[" + OBJECT_PROGRAM_STAGE_DATAELEMENT + SEPARATOR_OBJECT + "([0-9]+" + SEPARATOR_ID + "[0-9]+" + "\\])"; try { StatementHolder holder = statementManager.getHolder(); Statement statement = holder.getStatement(); ResultSet resultSet = statement.executeQuery( "SELECT caseaggregationconditionid, aggregationExpression FROM caseaggregationcondition"); while (resultSet.next()) { StringBuffer formular = new StringBuffer(); // --------------------------------------------------------------------- // parse expressions // --------------------------------------------------------------------- Pattern pattern = Pattern.compile(regExp); String expression = resultSet.getString(2).replaceAll("'", "''"); Matcher matcher = pattern.matcher(expression); while (matcher.find()) { String match = matcher.group(); match = match.replaceAll("[\\[\\]]", ""); String[] info = match.split(SEPARATOR_OBJECT); String[] ids = info[1].split(SEPARATOR_ID); int programStageId = Integer.parseInt(ids[0]); StatementHolder _holder = statementManager.getHolder(); Statement _statement = _holder.getStatement(); ResultSet rsProgramId = _statement.executeQuery( "SELECT programid FROM programstage where programstageid=" + programStageId); if (rsProgramId.next()) { int programId = rsProgramId.getInt(1); String aggregationExpression = "[" + OBJECT_PROGRAM_STAGE_DATAELEMENT + SEPARATOR_OBJECT + programId + SEPARATOR_ID + programStageId + SEPARATOR_ID + ids[1] + "]"; matcher.appendReplacement(formular, aggregationExpression); } } matcher.appendTail(formular); executeSql( "UPDATE caseaggregationcondition SET aggregationExpression='" + formular.toString() + "' WHERE caseaggregationconditionid=" + resultSet.getInt(1)); } } catch (Exception e) { e.printStackTrace(); } }