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();
}
}