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