// -------------------------------------------------------------------------
  // 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;
    }
  }
Пример #2
0
  public String execute() {
    System.out.println("Reported Date " + reportedDate);

    // Map<String, String> llElementValuesMap = new HashMap<String,
    // String>();
    List<LineListElement> lineListElements;

    List<LineListDataValue> llDataValuesList = new ArrayList<LineListDataValue>();
    List<LineListDataValue> llDataValuesUpdatedList = new ArrayList<LineListDataValue>();

    OrganisationUnit organisationUnit = selectedStateManager.getSelectedOrganisationUnit();

    // period = periodService.getPeriod(period.getStartDate(),
    // period.getEndDate(), period.getPeriodType());
    lineListGroup = selectedStateManager.getSelectedLineListGroup();

    Period period;

    if (lineListGroup != null
        && lineListGroup.getPeriodType().getName().equalsIgnoreCase("OnChange")) {
      period = periodService.getPeriod(0);
    } else {
      period = selectedStateManager.getSelectedPeriod();

      period = reloadPeriodForceAdd(period);
    }

    // String parts[] = dataElementId.split( ":" );

    ActionContext ctx = ActionContext.getContext();
    HttpServletRequest req = (HttpServletRequest) ctx.get(ServletActionContext.HTTP_REQUEST);
    session = req.getSession();

    int totalRecords = Integer.parseInt(req.getParameter("totalRecords"));
    int recordsFromDb = Integer.parseInt(req.getParameter("recordsFromDb"));

    lineListElements = new ArrayList<LineListElement>(lineListGroup.getLineListElements());

    String recordNumbersList = req.getParameter("recordNumbersList");
    System.out.println(recordNumbersList);
    // this code is for edited value saving
    String[] recordNos = recordNumbersList.split(":");
    for (int j = 0; j < recordNos.length; j++) {
      String recordNo = recordNos[j];
      System.out.println("recordNo = " + recordNo);
      if (!(recordNo.equals(""))) {
        String valueChangedName = "changedValue:" + recordNo;
        String valueChanged = req.getParameter(valueChangedName);
        if (!(valueChanged.equalsIgnoreCase(""))) {
          System.out.println("valueChanged = " + valueChanged);
          String[] elementNames = valueChanged.split(" ");
          LineListDataValue llDataValue = new LineListDataValue();
          Map<String, String> llElementValuesMap = new HashMap<String, String>();
          for (int e = 0; e < elementNames.length; e++) {
            String name = elementNames[e] + ":" + recordNo;
            String dataValue = req.getParameter(name);
            // System.out.println("name = " + name + " value  = " +
            // dataValue);
            if (dataValue != null && dataValue.trim().equals("")) {
              dataValue = "";
            }
            // if(dataValue.equals( "" ))
            llElementValuesMap.put(elementNames[e], dataValue);
            // System.out.println("llElementValuesMap size = "+
            // llElementValuesMap.size() + " key = "+elementNames[e]
            // + " value = "+llElementValuesMap.get( elementNames[e]
            // ));

          }

          // add map in linelist data value
          llDataValue.setLineListValues(llElementValuesMap);

          // add recordNumber to pass to the update query
          llDataValue.setRecordNumber(Integer.parseInt(recordNo));

          // add stored by, timestamp in linelist data value
          storedBy = currentUserService.getCurrentUsername();

          if (storedBy == null) {
            storedBy = "[unknown]";
          }

          llDataValue.setStoredBy(storedBy);

          if (reportedDate != null) {
            Date reportDate = format.parseDate(reportedDate);
            llDataValue.setTimestamp(reportDate);
          } else {
            llDataValue.setTimestamp(new Date());
          }

          llDataValuesUpdatedList.add(llDataValue);
        }
      }
    }

    // this code is for newly added values save
    while (recordsFromDb < totalRecords) {
      System.out.println("recordsFromDb = " + recordsFromDb);
      System.out.println("totalRecords = " + totalRecords);
      recordsFromDb++;
      LineListDataValue llDataValue = new LineListDataValue();
      Map<String, String> llElementValuesMap = new HashMap<String, String>();
      for (LineListElement element : lineListElements) {

        String name = element.getShortName() + ":" + recordsFromDb;
        String dataValue = req.getParameter(name);
        System.out.println("name = " + name + " value  = " + req.getParameter(name));

        if (dataValue != null && dataValue.trim().equals("")) {
          dataValue = "";
        }
        if (!(dataValue.equals(""))) llElementValuesMap.put(element.getShortName(), dataValue);
      }

      // add map in linelist data value
      llDataValue.setLineListValues(llElementValuesMap);

      // add period source, stored by, timestamp in linelist data value
      llDataValue.setPeriod(period);
      llDataValue.setSource(organisationUnit);

      storedBy = currentUserService.getCurrentUsername();

      if (storedBy == null) {
        storedBy = "[unknown]";
      }

      llDataValue.setStoredBy(storedBy);

      if (reportedDate != null && !reportedDate.trim().equalsIgnoreCase("")) {
        Date reportDate = format.parseDate(reportedDate);
        llDataValue.setTimestamp(reportDate);
      } else {
        llDataValue.setTimestamp(new Date());
      }

      llDataValuesList.add(llDataValue);
    }

    if (llDataValuesList.isEmpty() || llDataValuesList == null) {

      // deleteLLValue();

    } else {

      boolean valueInserted =
          dataBaseManagerInterface.insertLLValueIntoDb(
              llDataValuesList, lineListGroup.getShortName());
      System.out.println("valueInserted = " + valueInserted);
    }

    if (llDataValuesUpdatedList.isEmpty() || llDataValuesUpdatedList == null) {

    } else {
      boolean updateLLValue =
          dataBaseManagerInterface.updateLLValue(
              llDataValuesUpdatedList, lineListGroup.getShortName());
      System.out.println("updateLLValue = " + updateLLValue);
    }
    if (delRecordNo != null) {
      deleteLLValue();
    }
    return SUCCESS;
  }
Пример #3
0
 private final Period reloadPeriod(Period period) {
   return periodService.getPeriod(
       period.getStartDate(), period.getEndDate(), period.getPeriodType());
 }
  // -------------------------------------------------------------------------
  // 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;
  }