dhis2-devs team mailing list archive
-
dhis2-devs team
-
Mailing list archive
-
Message #07195
[Branch ~dhis2-devs-core/dhis2/trunk] Rev 2034: Fixed bug <618096> Periods not in good sequence in databrowser
------------------------------------------------------------
revno: 2034
committer: Hieu <hieu.hispvietnam@xxxxxxxxx>
branch nick: trunk
timestamp: Wed 2010-09-01 16:34:51 +0700
message:
Fixed bug <618096> Periods not in good sequence in databrowser
Fixed sql in MySQL by changing all alias or column name and table name to be lower-case
modified:
dhis-2/dhis-api/src/main/java/org/hisp/dhis/databrowser/DataBrowserTable.java
dhis-2/dhis-api/src/main/java/org/hisp/dhis/expression/ExpressionService.java
dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/jdbc/StatementManagerDataBrowserStore.java
dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/expression/DefaultExpressionService.java
dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/addProgramStageForm.vm
--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk
Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/databrowser/DataBrowserTable.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/databrowser/DataBrowserTable.java 2010-04-14 09:23:51 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/databrowser/DataBrowserTable.java 2010-09-01 09:34:51 +0000
@@ -126,11 +126,11 @@
ResultSetMetaData data = resultSet.getMetaData();
if ( data.getColumnCount() == 5 )
{
- if ( data.getColumnLabel( 5 ).equalsIgnoreCase( "ColumnHeader" ) )
+ if ( data.getColumnLabel( 5 ).equalsIgnoreCase( "columnheader" ) )
{
hasColumnName = true;
}
- if ( data.getColumnLabel( 4 ).equalsIgnoreCase( "PeriodId" ) )
+ if ( data.getColumnLabel( 4 ).equalsIgnoreCase( "periodid" ) )
{
hasPeriodIds = true;
}
=== modified file 'dhis-2/dhis-api/src/main/java/org/hisp/dhis/expression/ExpressionService.java'
--- dhis-2/dhis-api/src/main/java/org/hisp/dhis/expression/ExpressionService.java 2010-08-31 08:14:32 +0000
+++ dhis-2/dhis-api/src/main/java/org/hisp/dhis/expression/ExpressionService.java 2010-09-01 09:34:51 +0000
@@ -198,4 +198,5 @@
* @return A numerical expression.
*/
String generateExpression( String expression, Period period, Source source, boolean nullIfNoValues, boolean aggregated );
+
}
=== modified file 'dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/jdbc/StatementManagerDataBrowserStore.java'
--- dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/jdbc/StatementManagerDataBrowserStore.java 2010-08-19 20:13:13 +0000
+++ dhis-2/dhis-services/dhis-service-administration/src/main/java/org/hisp/dhis/databrowser/jdbc/StatementManagerDataBrowserStore.java 2010-09-01 09:34:51 +0000
@@ -16,7 +16,8 @@
/**
* @author joakibj, martinwa, briane, eivinhb
- * @version $Id StatementManagerDataBrowserStore.java 2010-04-06 Jason Pickering, Dang Duy Hieu$
+ * @version $Id StatementManagerDataBrowserStore.java 2010-04-06 jasonpp,
+ * ddhieu$
*/
public class StatementManagerDataBrowserStore
implements DataBrowserStore
@@ -51,7 +52,7 @@
try
{
StringBuffer sqlsb = new StringBuffer();
- sqlsb.append( "(SELECT d.datasetid AS ID, d.name AS DataSet, COUNT(*) AS counts_of_aggregated_values " );
+ sqlsb.append( "(SELECT d.datasetid AS id, d.name AS dataset, COUNT(*) AS counts_of_aggregated_values " );
sqlsb.append( "FROM datavalue dv " );
sqlsb.append( "JOIN datasetmembers dsm ON (dv.dataelementid = dsm.dataelementid) " );
sqlsb.append( "JOIN dataset d ON (d.datasetid = dsm.datasetid) " );
@@ -96,7 +97,8 @@
try
{
StringBuffer sqlsb = new StringBuffer();
- sqlsb.append( "(SELECT d.dataelementgroupid AS ID, d.name AS DataElementGroup, COUNT(*) AS counts_of_aggregated_values " );
+ sqlsb
+ .append( "(SELECT d.dataelementgroupid AS id, d.name AS dataelementgroup, COUNT(*) AS counts_of_aggregated_values " );
sqlsb.append( "FROM datavalue dv " );
sqlsb.append( "JOIN dataelementgroupmembers degm ON (dv.dataelementid = degm.dataelementid)" );
sqlsb.append( "JOIN dataelementgroup d ON (d.dataelementgroupid = degm.dataelementgroupid) " );
@@ -142,10 +144,11 @@
{
StringBuffer sqlsb = new StringBuffer();
- sqlsb.append( "(SELECT oug.orgunitgroupid, oug.name AS OrgUnitGroup, COUNT(*) AS counts_of_aggregated_values " );
+ sqlsb
+ .append( "(SELECT oug.orgunitgroupid, oug.name AS orgunitgroup, COUNT(*) AS counts_of_aggregated_values " );
sqlsb.append( "FROM orgunitgroup oug " );
sqlsb.append( "JOIN orgunitgroupmembers ougm ON oug.orgunitgroupid = ougm.orgunitgroupid " );
- sqlsb.append( "JOIN organisationunit ou ON ougm.organisationunitid = ou.organisationunitid " );
+ sqlsb.append( "JOIN organisationunit ou ON ougm.organisationunitid = ou.organisationunitid " );
sqlsb.append( "JOIN datavalue dv ON ou.organisationunitid = dv.sourceid " );
sqlsb.append( "WHERE dv.periodid IN " + splitListHelper( betweenPeriodIds ) + " " );
sqlsb.append( "GROUP BY oug.orgunitgroupid, oug.name " );
@@ -186,7 +189,7 @@
try
{
StringBuffer sqlsb = new StringBuffer();
- sqlsb.append( "(SELECT de.dataelementid, de.name AS DataElement " );
+ sqlsb.append( "(SELECT de.dataelementid, de.name AS dataelement " );
sqlsb.append( "FROM dataelement de JOIN datavalue dv ON (de.dataelementid = dv.dataelementid) " );
sqlsb.append( "JOIN datasetmembers dsm ON (de.dataelementid = dsm.dataelementid) " );
sqlsb.append( "WHERE dsm.datasetid = '" + dataSetId + "' " );
@@ -225,7 +228,7 @@
try
{
- sqlsb.append( "(SELECT deg.dataelementgroupid, deg.name AS DataElementGroup " );
+ sqlsb.append( "(SELECT deg.dataelementgroupid, deg.name AS dataelementgroup " );
sqlsb.append( "FROM dataelementgroup deg " );
sqlsb.append( "JOIN dataelementgroupmembers degm ON deg.dataelementgroupid = degm.dataelementgroupid " );
sqlsb.append( "JOIN datavalue dv ON degm.dataelementid = dv.dataelementid " );
@@ -265,7 +268,7 @@
try
{
StringBuffer sqlsb = new StringBuffer();
- sqlsb.append( "(SELECT de.dataelementid, de.name AS DataElement " );
+ sqlsb.append( "(SELECT de.dataelementid, de.name AS dataelement " );
sqlsb.append( "FROM dataelement de JOIN datavalue dv ON (de.dataelementid = dv.dataelementid) " );
sqlsb.append( "JOIN dataelementgroupmembers degm ON (de.dataelementid = degm.dataelementid) " );
sqlsb.append( "WHERE degm.dataelementgroupid = '" + dataElementGroupId + "' " );
@@ -303,7 +306,7 @@
try
{
StringBuffer sqlsb = new StringBuffer();
- sqlsb.append( "(SELECT o.organisationunitid, o.name AS OrganisationUnit " );
+ sqlsb.append( "(SELECT o.organisationunitid, o.name AS organisationunit " );
sqlsb.append( "FROM organisationunit o " );
sqlsb.append( "WHERE o.parentid = '" + orgUnitParent + "' " );
sqlsb.append( "ORDER BY o.name)" );
@@ -339,7 +342,7 @@
try
{
StringBuffer sqlsb = new StringBuffer();
- sqlsb.append( "(SELECT de.dataelementid, de.name AS DataElement " );
+ sqlsb.append( "(SELECT de.dataelementid, de.name AS dataelement " );
sqlsb.append( "FROM dataelement AS de " );
sqlsb.append( "INNER JOIN datavalue AS dv ON (de.dataelementid = dv.dataelementid) " );
sqlsb.append( "INNER JOIN datasetmembers AS dsm ON (de.dataelementid = dsm.dataelementid) " );
@@ -390,14 +393,15 @@
{
i++;
- sqlsb.append( "(SELECT de.dataelementid, de.name AS DataElement, Count(dv.value) AS counts_of_aggregated_values, p.periodid AS PeriodId, p.startDate AS ColumnHeader " );
+ sqlsb
+ .append( "(SELECT de.dataelementid, de.name AS dataelement, COUNT(dv.value) AS counts_of_aggregated_values, p.periodid AS periodid, p.startdate AS columnheader " );
sqlsb.append( "FROM dataelement de JOIN datavalue dv ON (de.dataelementid = dv.dataelementid) " );
sqlsb.append( "JOIN datasetmembers dsm ON (de.dataelementid = dsm.dataelementid) " );
sqlsb.append( "JOIN period p ON (dv.periodid = p.periodid) " );
sqlsb.append( "WHERE dsm.datasetid = '" + dataSetId + "' AND dv.periodid = '" + periodId + "' " );
- sqlsb.append( "GROUP BY de.dataelementid, de.name, p.periodid, p.startDate)" );
+ sqlsb.append( "GROUP BY de.dataelementid, de.name, p.periodid, p.startdate)" );
- sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY PeriodId " : "\n UNION \n" );
+ sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY columnheader " : "\n UNION \n" );
}
try
@@ -436,15 +440,16 @@
{
i++;
- sqlsb.append( "(SELECT de.dataelementid, de.name AS DataElement, COUNT(dv.value) AS counts_of_aggregated_values, p.periodid AS PeriodId, p.startDate AS ColumnHeader " );
+ sqlsb
+ .append( "(SELECT de.dataelementid, de.name AS dataelement, COUNT(dv.value) AS counts_of_aggregated_values, p.periodid AS periodid, p.startdate AS columnheader " );
sqlsb.append( "FROM dataelement de JOIN datavalue dv ON (de.dataelementid = dv.dataelementid) " );
sqlsb.append( "JOIN dataelementgroupmembers degm ON (de.dataelementid = degm.dataelementid) " );
sqlsb.append( "JOIN period p ON (dv.periodid = p.periodid) " );
sqlsb.append( "WHERE degm.dataelementgroupid = '" + dataElementGroupId + "' " );
sqlsb.append( "AND dv.periodid = '" + periodid + "' " );
- sqlsb.append( "GROUP BY de.dataelementid, de.name, p.periodid, p.startDate) " );
+ sqlsb.append( "GROUP BY de.dataelementid, de.name, p.periodid, p.startdate) " );
- sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY PeriodId " : "\n UNION \n" );
+ sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY columnheader " : "\n UNION \n" );
}
try
@@ -483,17 +488,20 @@
{
i++;
- sqlsb.append( " (SELECT deg.dataelementgroupid, deg.name, COUNT(dv.value) AS counts_of_aggregated_values, p.periodid AS PeriodId, p.startdate AS ColumnHeader " );
+ sqlsb
+ .append( " (SELECT deg.dataelementgroupid, deg.name, COUNT(dv.value) AS counts_of_aggregated_values, p.periodid AS periodid, p.startdate AS columnheader " );
sqlsb.append( "FROM dataelementgroup AS deg " );
- sqlsb.append( "INNER JOIN dataelementgroupmembers AS degm ON deg.dataelementgroupid = degm.dataelementgroupid " );
+ sqlsb
+ .append( "INNER JOIN dataelementgroupmembers AS degm ON deg.dataelementgroupid = degm.dataelementgroupid " );
sqlsb.append( "INNER JOIN datavalue AS dv ON degm.dataelementid = dv.dataelementid " );
sqlsb.append( "INNER JOIN period AS p ON dv.periodid = p.periodid " );
sqlsb.append( "INNER JOIN organisationunit AS ou ON dv.sourceid = ou.organisationunitid " );
sqlsb.append( "INNER JOIN orgunitgroupmembers AS ougm ON ou.organisationunitid = ougm.organisationunitid " );
- sqlsb.append( "WHERE p.periodid = '" + periodid + "' AND ougm.orgunitgroupid = '" + orgUnitGroupId + "' " );
+ sqlsb
+ .append( "WHERE p.periodid = '" + periodid + "' AND ougm.orgunitgroupid = '" + orgUnitGroupId + "' " );
sqlsb.append( "GROUP BY deg.dataelementgroupid,deg.name,p.periodid,p.startdate) " );
- sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY PeriodId " : "\n UNION \n" );
+ sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY columnheader " : "\n UNION \n" );
}
try
@@ -535,7 +543,7 @@
try
{
TimeUtils.start();
-
+
holder.getStatement().executeUpdate( sqlsbDescentdants.toString() );
setUpQueryForDrillDownViewTable( sqlsbDescentdants );
@@ -547,7 +555,7 @@
table.incrementQueryCount();
numResults = table.addColumnToAllRows( resultSet );
-
+
TimeUtils.stop();
}
catch ( SQLException e )
@@ -575,7 +583,8 @@
{
i++;
- sqlsb.append( "(SELECT de.dataelementid, de.name AS DataElement, Count(dv.value) AS counts_of_aggregated_values, p.periodid AS PeriodId, p.startDate AS ColumnHeader " );
+ sqlsb
+ .append( "(SELECT de.dataelementid, de.name AS dataelement, COUNT(dv.value) AS counts_of_aggregated_values, p.periodid AS periodid, p.startdate AS columnheader " );
sqlsb.append( "FROM dataelement AS de " );
sqlsb.append( "INNER JOIN datavalue AS dv ON (de.dataelementid = dv.dataelementid) " );
sqlsb.append( "INNER JOIN datasetmembers AS dsm ON (de.dataelementid = dsm.dataelementid) " );
@@ -583,9 +592,9 @@
sqlsb.append( "JOIN period p ON (dv.periodid = p.periodid) " );
sqlsb.append( "WHERE o.organisationunitid = '" + orgUnitId + "' " );
sqlsb.append( "AND dv.periodid = '" + periodId + "' " );
- sqlsb.append( "GROUP BY de.dataelementid, de.name, p.periodid, p.startDate)" );
+ sqlsb.append( "GROUP BY de.dataelementid, de.name, p.periodid, p.startdate)" );
- sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY PeriodId " : "\n UNION \n" );
+ sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY columnheader " : "\n UNION \n" );
}
try
@@ -674,19 +683,25 @@
/**
* The current organization unit
*/
- sb.append( "SELECT DISTINCT o.organisationunitid AS parentid, o.name AS OrganisationUnit, COUNT(value) as countdv_descendants, p.periodid AS PeriodId, p.startDate AS ColumnHeader " );
+ sb
+ .append( "SELECT DISTINCT o.organisationunitid AS parentid, o.name AS organisationunit, COUNT(value) as countdv_descendants, p.periodid AS periodid, p.startdate AS columnheader " );
sb.append( "FROM organisationunit o " );
sb.append( "JOIN datavalue dv ON ( dv.sourceid = o.organisationunitid ) " );
sb.append( "JOIN period p ON ( p.periodid = dv.periodid ) " );
sb.append( "WHERE o.parentid = '" + orgUnitSelected + "' " );
sb.append( "AND dv.periodid = '" + periodid + "' " );
- sb.append( "GROUP BY o.organisationunitid, OrganisationUnit, p.periodid, p.startdate " );
+ sb.append( "GROUP BY o.organisationunitid, organisationunit, p.periodid, p.startdate " );
sb.append( "UNION " );
/**
* All descendant levels of selected organization unit
*/
- sb.append( "SELECT DISTINCT ou" + orgIndex + ".organisationunitid AS parentid, ou" + orgIndex + ".name AS OrganisationUnit, COUNT(value) as countdv_descendants, p.periodid AS PeriodId, p.startDate AS ColumnHeader " );
+ sb
+ .append( "SELECT DISTINCT ou"
+ + orgIndex
+ + ".organisationunitid AS parentid, ou"
+ + orgIndex
+ + ".name AS organisationunit, COUNT(value) as countdv_descendants, p.periodid AS periodid, p.startdate AS columnheader " );
sb.append( "FROM datavalue dv " );
sb.append( "JOIN organisationunit ou ON ( ou.organisationunitid = dv.sourceid ) " );
this.setUpQueryForJOINTable( sb, diffLevel );
@@ -696,10 +711,10 @@
sb.append( "( " );
sb.append( this.setUpQueryGetDescendants( curLevel, maxLevel, orgUnitSelected ) );
sb.append( " ) " );
- sb.append( "GROUP BY ou" + orgIndex + ".organisationunitid, OrganisationUnit, p.periodid, p.startDate " );
-
+ sb.append( "GROUP BY ou" + orgIndex + ".organisationunitid, organisationunit, p.periodid, p.startdate " );
+
sb.append( i < loopSize ? "UNION " : "" );
-
+
}
}
@@ -712,7 +727,7 @@
for ( j++; j < (maxLevel); j++ )
{
- oldSQL = "SELECT DISTINCT idlevel" + (j + 1) + " as descendant FROM _orgunitstructure os WHERE idlevel"
+ oldSQL = "SELECT DISTINCT idlevel" + (j + 1) + " AS descendant FROM _orgunitstructure os WHERE idlevel"
+ (j) + " IN ( " + oldSQL + " ) ";
}
@@ -723,17 +738,18 @@
{
sb.delete( 0, sb.capacity() );
- sb.append( "SELECT parentid, organisationunit, sum(countdv_descendants) AS counts_of_aggregated_values, periodid, columnheader " );
+ sb
+ .append( "SELECT parentid, organisationunit, SUM(countdv_descendants) AS counts_of_aggregated_values, periodid, columnheader " );
sb.append( "FROM view_count_descentdants " );
sb.append( "GROUP BY parentid, organisationunit, periodid, columnheader " );
- sb.append( "ORDER BY periodid; " );
+ sb.append( "ORDER BY columnheader; " );
}
private void setUpQueryForJOINTable( StringBuffer sb, int diffLevel )
{
for ( int i = 1; i < diffLevel; i++ )
{
- sb.append( "JOIN OrganisationUnit ou" + (i) + " ON ( ou" + (((i == 1) && (i != 0)) ? "" : (i - 1))
+ sb.append( "JOIN organisationunit ou" + (i) + " ON ( ou" + (((i == 1) && (i != 0)) ? "" : (i - 1))
+ ".parentid = ou" + (i) + ".organisationunitid ) " );
}
}
=== modified file 'dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/expression/DefaultExpressionService.java'
--- dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/expression/DefaultExpressionService.java 2010-08-31 08:14:32 +0000
+++ dhis-2/dhis-services/dhis-service-core/src/main/java/org/hisp/dhis/expression/DefaultExpressionService.java 2010-09-01 09:34:51 +0000
@@ -469,4 +469,5 @@
return new DataElementOperand( dataElementId, categoryOptionComboId );
}
+
}
=== modified file 'dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/addProgramStageForm.vm'
--- dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/addProgramStageForm.vm 2010-02-22 09:19:15 +0000
+++ dhis-2/dhis-web/dhis-web-maintenance/dhis-web-maintenance-patient/src/main/webapp/dhis-web-maintenance-patient/addProgramStageForm.vm 2010-09-01 09:34:51 +0000
@@ -1,6 +1,6 @@
<h3>$i18n.getString( "add_new_program_stage" )</h3>
-<form id="addProgramStageForm" name "addProgramStageForm" action="addProgramStage.action" method="post" onsubmit="submitForm(this);return validateAddProgramStage(); ">
+<form id="addProgramStageForm" name="addProgramStageForm" action="addProgramStage.action" method="post" onsubmit="submitForm(this);return validateAddProgramStage();">
<div style="display:inline">
